topic Re: looping through a list to create small dataset in SAS Programming

looping through a list to create small dataset

CP2 — Thu, 08 Mar 2018 16:39:22 GMT

I want to create a dataset from a list because merging to a proc sql or sql pass through might be easier to manage. I know this must be simple but it doesn't like the scan. The list is separated by commas.

data druglist ;
format drug $200. ;
%let ct=%sysfunc(countw(%quote(&druglist.), %str(,) )) ;
%put &ct. ;
do i = 1 to &ct ;
drug = scan(&druglist, i) ;
output ;
end ;
run;
proc print ; run;

log:

24 data druglist ;
25 format drug $200. ;
26 %let ct=%sysfunc(countw(%quote(&druglist.), %str(,) )) ;
27 %put &ct. ;
6
28 do i = 1 to &ct ;
29 drug = scan(&druglist, i) ;
____
72
ERROR 72-185: The SCAN function call has too many arguments.

30 output ;
31 end ;
32 run;

Re: looping through a list to create small dataset

SuryaKiran — Thu, 08 Mar 2018 16:44:36 GMT

Double quote the list for macro variable.

data druglist ;
format drug $200. ;
%let ct=%sysfunc(countw(%quote(&druglist.), %str(,) )) ;
%put &ct. ;
do i = 1 to &ct ;
drug = scan("&druglist", i) ;
output ;
end ;
run;
proc print ; run;

Re: looping through a list to create small dataset

Reeza — Thu, 08 Mar 2018 16:49:24 GMT

You're mixing macro and data step code:

data druglist ;

format drug $200. ;

 ct=countw("&druglist") ;

do i = 1 to ct ;
    drug = scan("&druglist", i) ;
    output ;
end ;

run;

proc print data=druglist;
run;

Re: looping through a list to create small dataset

ballardw — Thu, 08 Mar 2018 17:03:30 GMT

If your "druglist" macro variable has commas then that will cause the error. If your variable has individually quoted values then you'll get a different error.

it would help to show what your "druglist" looks like.

And you are way overusing macro functions where not needed and incorrect.

%let druglist = a bdq cccccc dddddddddd;
data example ;
do i = 1 to (countw("&druglist"));
   drug = scan("&druglist.", i) ;
   output;
end; ;
run;

You should specify a length for DRUG as otherwise the length is going to be the length of the macro variable.

Re: looping through a list to create small dataset

CP2 — Thu, 08 Mar 2018 17:05:22 GMT

omg - working with too many macros and proc sqls lately I guess. Thank you!