CODEWITHSUNDEEP
sassas-macroproc-sql
tita
tita

Reputation: 107

SAS Array creation

I am trying to create array that hold a value. 

proc sql noprint;
select count(*) into :dscnt from study;
select libname into :libname1 - :libname&dscnt from study;
quit;

I think the syntax is correct but i keep getting this following error message in in SAS studio.

***NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Line generated by the macro variable "DSCNT".
79         libname       4
                      _
                      22
                      200
ERROR 22-322: Syntax error, expecting one of the following: ',', FROM, NOTRIM.  

ERROR 200-322: The symbol is not recognized and will be ignored.***

Can someone explain to me what i am doing wrong?

Thanks

Upvotes: 3

Views: 177

Answers (3)

Reeza
Reeza

Reputation: 21294

You do not need to know the number of items ahead of time, if you leave it blank, SAS will automatically create the correct number of macro variables.

If you do want to use that number elsewhere you can create it using the TRIMMED option to remove any extra spaces. See the second example below.

proc sql noprint;
select name into :name1- from sashelp.class;
quit;

%put &name1;
%put &name19.;

proc sql noprint;
select count(distinct name) into :name_count TRIMMED from sashelp.class;
quit;

%put &name_count;

Results:

3068      proc sql noprint;
3069      select name into :name1- from sashelp.class;
3070      quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


3071
3072      %put &name1;
Alfred
3073      %put &name19.;
William
3074
3075      proc sql noprint;
3076      select count(distinct name) into :name_count TRIMMED from
3076! sashelp.class;
3077      quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


3078
3079      %put &name_count;
19

Upvotes: 5

Robert Penridge
Robert Penridge

Reputation: 8513

The into syntax in proc sql stores formatted values into macro variables. For example if you run this code:

proc sql noprint;
  select count(*) into :dscnt from sashelp.class;
quit;

%put #&dscnt#;

You will see the output is:

#      19#

In otherwords the result is left padded with spaces. This means in your example, the code is resolving to something like:

select libname into :libname1 - :libname      19 from study;

^ Which is obviously invalid syntax. To fix this, you can simply add the TRIMMED keyword to your SQL statement:

select count(*) into :dscnt TRIMMED from study;

Thanks to Reeza for the TRIMMED keyword.

Upvotes: 1

Kiran
Kiran

Reputation: 3315

do something like below

 proc sql noprint;
 select count(*) into :dscnt from sashelp.class;
  select name into :name1 - :name%left(&dscnt) from sashelp.class;
  quit;

Upvotes: 0

Related Questions