Date: Mon, 22 Jun 2009 02:11:18 -0700
Reply-To: Amar Mundankar <amarmundankar@GMAIL.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Amar Mundankar <amarmundankar@GMAIL.COM>
Organization: http://groups.google.com
Subject: Re: Proc sql select :into and macro variables
Content-Type: text/plain; charset=UTF-8
On Jun 22, 4:50Â am, "dc...@hotmail.com" <dc...@hotmail.com> wrote:
> Hi,
>
> could someone explain why the first case runs but the second case
> generates an error message?
>
> FIRST CASE
> 933 Â proc sql noprint;
> 934 Â select count(*) into :cnt
> 935 Â from (select distinct d_date from fs.test_mm);
> 936 Â %let max_cnt = &cnt;
> 937
> 938 Â select distinct d_date into :idx_n1-:idx_n&max_cnt
> 939 Â from fs.test_mm;
> 940 Â quit;
> NOTE: PROCEDURE SQL used:
>    real time      0.73 seconds
>    cpu time       0.71 seconds
> 941 Â data _null_;
> 942 Â put "&idx_n108";
> 943 Â run;
>
> 2008/11/28
> NOTE: DATA statement used:
>    real time      0.00 seconds
>    cpu time       0.00 seconds
>
> SECOND CASE
> 944 Â proc sql noprint;
> 945 Â select count(*) into :max_cnt
> 946 Â from (select distinct d_date from fs.test_mm);
> 947
> 948
> 949 Â select distinct d_date into :idx_n1-:idx_n&max_cnt
> 950 Â from fs.test_mm;
> NOTE: Line generated by the macro variable "MAX_CNT".
> 1   idx_n   109
> Â Â Â Â Â Â Â Â ---
> Â Â Â Â Â Â Â Â 22
> Â Â Â Â Â Â Â Â 76
> ERROR 22-322: Syntax error, expecting one of the following: ',', FROM,
> NOTRIM.
>
> ERROR 76-322: Syntax error, statement will be ignored.
>
> 951 Â quit;
> NOTE: The SAS System stopped processing this step because of errors.
> NOTE: PROCEDURE SQL used:
>    real time      0.40 seconds
>    cpu time       0.38 seconds
HI,
Following is a Extract from a book SAS Publishing SAS Certification
(which is a book by SAS for Advance SAS Certifiation).(Page 368 of
994). Look at the "note".
%let removes leading and trailing blanks but :into does not.
Example
You can create a macro variable named totalfee that contains the total
of all course
fees, and use this macro variable in a later step. You use the NOPRINT
option to
suppress the output from the PROC SQL step.
proc sql noprint;
select sum(fee) format=dollar10. into :totalfee
from sasuser.all;
quit;
%let totalfee=&totalfee;
proc means data=sasuser.all sum maxdec=0;
class course_title;
var fee;
title "Grand Total for All Courses Is &totalfee";
run;
Note: This form of the INTO clause does not trim leading or trailing
blanks, but the
%LET statement removes any leading or trailing blanks that are stored
in the value of
totalfee. ô€€€
Thanks and Regards,
Amar Mundankar.
| 
