10-12-2016 10:45 AM
proc sql noprint;
select compress(lowcase(memname)) into : dsnams separated by " "
from sashelp.vmember where libname="NEW" and memname like "AD%";
proc sort data=New.&dsnams out=&dsnams; by usubjid; run;
if first.usubjid then seqnum=1;
log Error :
New.adlbc adlbhy adqsadas adqscibc adqsnpix adsl adtte advs
ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA,
DATECOPY, DETAILS, DIAG, DUPOUT, EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE,
LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS, NODUPLICATE,
NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, OSA,
OUT, OVERWRITE, PAGESIZE, PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO,
SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS, WKNO, WORKNO.
ERROR 76-322: Syntax error, statement will be ignored.
10-12-2016 10:52 AM
Yes, that error is correct per the code you have provided. The proc sql is creating a macro variable which contains each of the text items returned, separated by a space and putting it into the macro variable called &dsnams. The program then moves onto the proc sort, and replaces the &dsnams. call with the text it represents, and as you know proc sort data=new.<long list of datasets> is not valid Base SAS code hence you get an error.
An easier way to code things might be:
data _null_; set sashelp.vtable (where=(libname="NEW" and substr(memname,1,2)="AD")); call execute(cats('proc sort data=new.',memname,'; by usubjid; run;')); call execute(cats('data new.',memname,'; set new.',memname,'; retain seqnum; by usubjid; if first.usubjid then seqnum=1; else seqnum=seqnum+1; run;')); run;
Note there are other problems in your original code - you don't retain seqnum for instance. I have not tested the above, but that should be near.
10-12-2016 11:16 AM
10-12-2016 01:48 PM
Yes, I completely understand that, and that is exactly what my code does. For each dataset returned by the where clause, the sort and id assignment datastep are generated for that datastep. Try it, you will find it far easier than the method you were attempting.
10-12-2016 10:58 AM
Wouldn't you expect to get an error if you submitted:
proc sort data=New.adlbc adlbhy adqsadas adqscibc adqsnpix adsl adtte advs ?
You can only sort one data set at a time. You have already received one suggestion about how to loop through a set of data set names. Here's another approach: