BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
UCL_Mike
Calcite | Level 5

Hello!

 

I hope someone can help me with what I'm sure is a simple issue cos I'm stumped!

 

I have a bunch of variables which are all indexed by the same variable (called specimen) and I want to pairwise inner join all of these variables together on specimen in separate outputs. I have created the macro below to help automate this:

 

%macro inner;
%let i = 1;
%let j = 2;
%do %while(&i < 3);
PROC SQL;
CREATE TABLE LI1_HOM_&i._&j AS SELECT
A.VAR_&i.,
B.VAR_&j
FROM LI1_HOM_&i A
INNER JOIN LI1_HOM_&j B
ON A.SPECIMEN=B.SPECIMEN
;
%let j = %eval(&j + 1);
%if &j = 4 %then %do;
	%let i = %eval(&i + 1);
	%let j = %eval(&i + 1);
	%end;
%end;
%mend;

I know that the code does what I want up to the %if statement, as the expected outputs appear. However, the code then tries to create tables where i = 1 and j keeps increasing suggesting that the %if statement isn't working.

 

The %if statement is designed to add 1 to i and to set j to i + 1 and then start joining more variables. In this (limited) test, the maximum value of i is 2 and the maximum value of j is 3 (so there will be 6 outputs) but once the macro works it will be used for much higher values of i and j.

 

Can someone explain what is going wrong with the %if statement?

 

I am using SAS 9.4 and working in Enterprise Guide 7.1.

 

Many thanks,

 

Mike

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

Why would you want to increment the loop counters manually?

Why aren't you just using normal iterative DO loops?  

%do i=1 %to 3 ;
  %do j=&i+1 %to 4 ;
...
  %end;
%end;

View solution in original post

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

First thing that jumps out at me is:

	%let j = %eval(&i + 1);

Did you mean to set j to i+1?

 

Second off, really not a good idea to create lots of datasets of the same data, its just going to make your life harder coding with it. 

Provide some sample test data and required output, bet there is a simpler and faster method. 

UCL_Mike
Calcite | Level 5

Thanks for the swift reply!

 

To answer your first point, I am not sure if I want j = i+1 or j = i+2. The macro starts with i = 1 and j = 2 and when the %if statement is processed for the first time (when j = 4) I want to move to i = 2 and j = 3.

 

Here's some input data:

 

Input 1  Input 2  Input 3 
SpecimenVAR_1 SpecimenVAR_2 SpecimenVAR_3
A 1003 A 1000 A 1002
A 300 A 761 A 300
A 763 SDR 10 A 761
SDR 12 K B1 SDR 10
K B2 K C1 K B1
K C2 K E0 K C0
K E1 K H0 K E1
K H1 K J0 K H0
K J1 K L0 K J0
K L1 LM 11 K L2
LM 11 M1 LM 10
M1 Ind I1 M0
Ind I3 Ind II0 Ind I0
Ind II2 Ind III1 Ind II0
Ind III2 Ind VII0 Ind III0
Ind VII3 Ind XV0 Ind VII0
Ind XV2 Ind XVI0 Ind XV1
Ind XVI2 Ind XVII0 Ind XVI0
Ind XVII2 Ind XX0 Ind XVII2
Ind XX3 Ind XXII1 Ind XX2
Ind XXII3 Ind XXIII0 Ind XXII1
Ind XXIII2 Ind XXIV0 Ind XXIII1
Ind XXIV2 Ind XXV0 Ind XXIV1
Ind XXV3 Ind XXVI0 Ind XXV1
Ind XXVI2 Ind XXXI0 Ind XXVI0
Ind XXXI2 LI1 isoa0 Ind XXXI0
LI1 isoa4 LI1 isob1 LI1 isoa0
LI1 isob2 LI1 isoc1 LI1 isob0
LI1 isoc3 LI1 isonu0 LI1 isoc0
LI1 isonu3 LI1 isox0 LI1 isonu0
LI1 isox3 SC 11 LI1 isox0
SC 11    SC 10

 

As you can see, Input 2 has no value for the specimen called A30 so it cannot inner join with the other two variables.

 

The outputs in this case would be:

 

Output 1   Output 2  Output 3  
SpecimenVAR_1VAR_2 SpecimenVAR_1VAR_3 SpecimenVAR_2VAR_3
A 10030 A 10032 A 10002
A 7631 A 3000 A 7611
SDR 120 A 7631 SDR 100
K B21 SDR 120 K B11
K C21 K B21 K C10
K E10 K C20 K E01
K H10 K E11 K H00
K J10 K H10 K J00
K L10 K J10 K L02
LM 111 K L12 LM 110
M11 LM 110 M10
Ind I31 M10 Ind I10
Ind II20 Ind I30 Ind II00
Ind III21 Ind II20 Ind III10
Ind VII30 Ind III20 Ind VII00
Ind XV20 Ind VII30 Ind XV01
Ind XVI20 Ind XV21 Ind XVI00
Ind XVII20 Ind XVI20 Ind XVII02
Ind XX30 Ind XVII22 Ind XX02
Ind XXII31 Ind XX32 Ind XXII11
Ind XXIII20 Ind XXII31 Ind XXIII01
Ind XXIV20 Ind XXIII21 Ind XXIV01
Ind XXV30 Ind XXIV21 Ind XXV01
Ind XXVI20 Ind XXV31 Ind XXVI00
Ind XXXI20 Ind XXVI20 Ind XXXI00
LI1 isoa40 Ind XXXI20 LI1 isoa00
LI1 isob21 LI1 isoa40 LI1 isob10
LI1 isoc31 LI1 isob20 LI1 isoc10
LI1 isonu30 LI1 isoc30 LI1 isonu00
LI1 isox30 LI1 isonu30 LI1 isox00
SC 111 LI1 isox30 SC 110
    SC 110    

 

There are many more variables with considerably more combinations of specimens.

 

My purpose is to create pairs of variables, then append additional data from another source (also on SAS) with different specimens but the same variables where the number of new specimens is equal to the existing specimens (this will be different for each pair). Then an analysis will be conducted on these datasets.

 

My requirement here is to automate the process of matching variables so that I don't have to do it manually.

 

Thanks for your help.

 

Mike

RW9
Diamond | Level 26 RW9
Diamond | Level 26

What you are trying to do is to create a map of all the possible combinations.  You can do this easier by:

Put all your input data into one dataset first, i.e. a long one then doing a cartesian join on the data.  Something like this post:

http://support.sas.com/documentation/cdl/en/sqlproc/63043/HTML/default/viewer.htm#n082a03omu3i21n1k8...

Tom
Super User Tom
Super User

Why not just use a normal MERGE statement to combine all of the input datasets into a single dataset with ALL of your measures?

If the dataset names have nice numeric suffixes then you can use a list of dataset names like this:

data want ;
  merge LI1_HOM_1 - LI1_HOM_20 ;
  by specimen ;
run;

You will need to make sure that they are sorted by SPECIMEN first.

So if you have 20 little datasets you could use a simple macro like this to sort them all.

%macro sort_them(basename,byvars,from,to);
%local i ;
%do i=&from %to &to ;
proc sort data &basename.&i ; 
  by &byvars;
run;
%end;
%mend sort_them;
%sort_them(basename=LI1_HOM_,byvars=SPECIMEN,from=1,to=20);
Kurt_Bremser
Super User

Before you start using the macro variables in code generated by the macro, use %put to check the "evolution" of your macro variables:

%macro inner;
%let i = 1;
%let j = 2;
%do %while(&i < 3);
  %put i=&i;
  %put j=&j;
  %let j = %eval(&j + 1);
  %if &j = 4 %then %do;
    %let i = %eval(&i + 1);
    %let j = %eval(&i + 1);
  %end;
%end;
%mend;
%inner

This will provide a clue where you made a mistake.

UCL_Mike
Calcite | Level 5

Hello and thanks for your reply.

 

I tried running this but just got a log telling me that it had created a SASREPORT:

 

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

 

I don't know how to utilise this method!

Tom
Super User Tom
Super User

Why would you want to increment the loop counters manually?

Why aren't you just using normal iterative DO loops?  

%do i=1 %to 3 ;
  %do j=&i+1 %to 4 ;
...
  %end;
%end;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 8 replies
  • 1667 views
  • 5 likes
  • 4 in conversation