CODEWITHSUNDEEP
sassas-macro
jiri jansa
jiri jansa

Reputation: 159

SAS - macro looping through pieces of text

My problem is following - I have a macro that is supposed to loop through pieces of strings and use these strings inside the sql procedure. The problem is that these strings are not simple and compact, there may be blank spaces, quotes.

The string input into the macro %roz is following:

list1,list2
OID,in ("IDC","NPK")
OED,in ("EDC")
ZFP,in ("ZFP")

The macro looks in this way:

%macro roz(list1,list2);
%let n=%sysfunc(countw(&list1));
 %do i=1 %to &n;
     %let kanal = %scan(&list1,&i);
     %let inlist = %scan(&list2,&i);
proc sql;
 create table data_stor1_&kanal._file as select sum(pocet)
  as pocet, Dch
  from data_stor3_&kanal where Dch &inlist group by dat_poc;
quit;

%end;
%mend roz;

%roz(OID OED,'in ("IDC","NPK")' 'in ("EDC")');

My problem is that I don't know how to make my macro to read the pieces in the list2 and preserve the format (quotes, blank spaces, e.t.c.). I can fix the problem earlier in my program by switching from strings to numbers but I am curious if there is a way how to solve it directly.

My two resulting sql "selects" are supposed to look in this way:

 create table data_stor1_OID_file as select sum(pocet) as pocet, Dch
 from data_stor3_OID where Dch in ("IDC","NPK") group by dat_poc;

 create table data_stor1_OED_file as select sum(pocet) as pocet, Dch
 from data_stor3_OED where Dch in ("EDC") group by dat_poc;

Thank you for any suggestions !

Upvotes: 0

Views: 391

Answers (1)

Joe
Joe

Reputation: 63434

Use a string delimiter for your list that is not space (and doesn't otherwise appear in your strings).

%macro roz(list1,list2);
%let n=%sysfunc(countw(&list1,|));
 %do i=1 %to &n;
     %let kanal = %scan(&list1,&i,|);
     %let inlist = %scan(&list2,&i,|);
proc sql;
 create table data_stor1_&kanal._file as select sum(pocet)
  as pocet, Dch
  from data_stor3_&kanal where Dch &inlist group by dat_poc;
quit;

%end;
%mend roz;

%roz(OID|OED,%nrstr(in ("IDC","NPK")|in ("EDC")));

Something like that. I don't thin you need single quotes - use macro quoting for the purpose of avoiding the comma, if you like, though I'm not sure you even need that.

Upvotes: 2

Related Questions