BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
manya92
Fluorite | Level 6
%macro icodes ;
	
	proc sql ;
		create table _01_itable2 as
		%do i = 1 %to 14 ;
			select distinct enrolid, dx&i. as code 
			from derived._01_itable 
			%if i <14 %then %do ;
				union
			%end ;
		%end ;
		;
	quit;

%mend icodes ;
%icodes;	

Whats wrong with this code ?

SAS is giving me this error :- NOTE: Line generated by the invoked macro "ICODES".
129 select distinct enrolid, dx&i. as code from derived._01_itable
________ ____
78 22
76
ERROR 78-322: Expecting a ','.

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

You are comparing the letter i to the digits 14

i < 14

Instead of testing the value of the macro variable i

&i < 14

View solution in original post

5 REPLIES 5
Astounding
PROC Star

Even if you get this to work, it seems extremely inefficient to read your data set 14 times just to be able to transpose the data.  Why not just use PROC TRANSPOSE?

manya92
Fluorite | Level 6

how to do that ? I'd like to see how to do it both by my code (macros) and proc transpose

PaigeMiller
Diamond | Level 26

Hint:

 

Use 

 

options mprint;

before calling the macro, then the error message will be much easier to debug.

 

Hint 2:

 

When including a SASLOG into this forum, use the {i} icon that will space the letters properly so we can see exactly what is underlined.

--
Paige Miller
Tom
Super User Tom
Super User

You are comparing the letter i to the digits 14

i < 14

Instead of testing the value of the macro variable i

&i < 14
Tom
Super User Tom
Super User

Just use PROC transpose

proc transpose data=derived._01_itable 
  out= _01_itable2 (rename=(col1=code)) 
;
 by enrolid ;
  var dx1-dx14 ;
run;

Although if you really do have multiple records per ENROLID you might need to first decide which one you want to transpose.

Or create a new variable that will uniquely id the rows that you can use in the BY statement for PROC TRANSPOSE and then add a PROC SORT NODUPKEY to remove the multiple copies of the same ENROLID, CODE combinations.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 902 views
  • 4 likes
  • 4 in conversation