Error while creating sequence number with macro variable for multiple datasets

Reply
Occasional Contributor
Posts: 17

Error while creating sequence number with macro variable for multiple datasets


proc sql noprint;
select compress(lowcase(memname)) into : dsnams separated by " "
from sashelp.vmember where libname="NEW" and memname like "AD%";
quit;
%put &dsnams;

proc sort data=New.&dsnams out=&dsnams; by usubjid; run;

data &dsnams;
set &dsnams;
by usubjid;
if first.usubjid then seqnum=1;
else seqnum+1;
run;

 

 

log Error :

New.adlbc adlbhy adqsadas adqscibc adqsnpix adsl adtte advs
------
22
76

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.

Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Error while creating sequence number with macro variable for multiple datasets

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.

Occasional Contributor
Posts: 17

Re: Error while creating sequence number with macro variable for multiple datasets

Thank you for your reply.
But exactly what I wanted is to create sequence number for multiple data
sets at a time based on subj-id. That is what i was tried.
First I wanted to get data set names from one library and assigned those
names to macro variable to use later those macro variables.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Error while creating sequence number with macro variable for multiple datasets

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.

Occasional Contributor
Posts: 17

Re: Error while creating sequence number with macro variable for multiple datasets

Got it. Thank you.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Respected Advisor
Posts: 5,005

Re: Error while creating sequence number with macro variable for multiple datasets

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:

 

http://blogs.sas.com/content/publishing/2015/01/30/sas-authors-tip-getting-the-macro-language-to-per...

 

Good luck.

Ask a Question
Discussion stats
  • 5 replies
  • 306 views
  • 0 likes
  • 3 in conversation