SAS Array creation

Question

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

You don't have to pre-count, but if you do remove the leading spaces. See this related answer https://stackoverflow.com/a/32525294/4965549 — Tom, Apr 27 '18 at 18:54

score 5 · Accepted Answer · answered Apr 27 '18 at 18:34

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

SAS will count the number of observations returned by the first query for you. If you need the count just save `&sqlobs`. https://stackoverflow.com/a/32525294/4965549 — Tom, Apr 27 '18 at 18:55
True, I was trying to demonstrate the TRIMMED option though, how to remove those extra spaces in the first place, which is nice to know for the future. — Reeza, Apr 27 '18 at 19:21

Robert Penridge · Answer 2 · 2018-04-27T18:32:45.783

1

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.

edited Apr 27 '18 at 18:32

answered Apr 27 '18 at 18:28

Robert Penridge

8,424
2
34
55

1

TRIMMED + you don't need to specify the last prefix ever. – Reeza Apr 27 '18 at 18:30
@Reeza Good tips, do you know if dropping the last prefix is only good for certain versions of SAS or has it been around a long time? – Robert Penridge Apr 27 '18 at 20:08
I think it was introduced in either 9.3 or 9.4 so relatively recently. – Reeza Apr 27 '18 at 20:53

score 0 · Answer 3 · answered Apr 27 '18 at 18:25

0

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;

answered Apr 27 '18 at 18:25

Kiran

3,255
3
14
21

SAS Array creation

3 Answers3