BookmarkSubscribeRSS Feed
apple
Calcite | Level 5

Using SAS EG 7.1.

 

Basically I want to merge

FATax&YEAR1.&MONTH with Y&YEAR2.&MONTH, but YEAR1 is NOT equal YEAR2.

 

For eg,

 

FATax2015.&MONTH with Y&2014.&MONTH

 

I tried the below, but it doesn't seem to work.

 

 

CODE:

 

%DO YEAR1=&FIRSTYR_Tax %TO &LASTYR_TAX;

%DO YEAR2=&FIRSTYR_Y %TO &LASTYR_Y;

%do i = 1 %to %sysfunc(countw(&monlist.));

%let month = %sysfunc(putn(%scan(&monlist.,&i.),z2.));

data Merged&YEAR1.&MONTH;

merge

FATax&YEAR1.&MONTH(IN=A) Y&YEAR2.&MONTH (IN=B);

BY UIN;

IF A AND B Or (A AND Age=0);

run;

 

%END;

%END;

%END;

 

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Why are you creating a rod for your own back in the first place?  I would first set your data together, get rid of the data elements in the dataset name - remember data set name, and variable names, are there for programming purposes Not to contain "data".  Labels and observations are there to contain "data".  If you put your data together, and you haven't posted any example data so I can't provide code, you can then simplfy your whole code to focus on one dataset, no need for looping, or macro or anything else, simple Base SAS programming.

 

If you can post some example test data - in the form of a datastep, I will show you how to process it into well strcutured data, and then how to program using Base SAS a very simple setup.

 

Edit: I also add the link to your other question, which is pretty much the same thing and caused by the same structural choices:

http://communities.sas.com/t5/Base-SAS-Programming/Macro-with-Merge-IN-A/m-p/258419/highlight/false#...

apple
Calcite | Level 5
HI,
I am no interested in "SET" as I want to merge by UIN.

I have many datasets, so using a macro will more efficient.
THank you
RW9
Diamond | Level 26 RW9
Diamond | Level 26

The merge is irrelevant to the strcuture of the data, it is exactly the same to merge one dataset with itself as many to many.

 

With regards to your second point, macro will not be more efficient.  This is a fundamental lack of understanding of what macro is.  Macro is a text generation tool, which generates Base SAS code.  It and of itself does nothing.  So the question is, is the code generated by the macro processor more efficient and than the datastep I provided.  Now I haven't tested it, but as mine is one datastep, with one datastep, and therefore would incur only one read/write per operation, whereas the macro code would generate one step with one read/write per operation I would conclude that no, the macro method would be slower, not to mention the code itself is far more complicated to maintain.

 

The simple fact is that you have many datasets, which you are reading, and them merging together.  A simpler method, is to put them all together in one step and then transpose the resulting dataset.  This would have exactly the same output as your method, with two datasteps.

 

If there was some test data, I could proide code.

Kurt_Bremser
Super User

With this construct, the macro will create datasteps where

number of datasteps = cardinality of YEAR1 * cardinality of YEAR2 * cardinality of &monlist

which is a whole lot of datasteps

 

I guess you have some rule for the relationship of YEAR2 to YEAR1?

eg if the difference is 1, then

%DO YEAR1=&FIRSTYR_Tax %TO &LASTYR_TAX;
%let year2 = %eval(&year1-1);

should suffice

 

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

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
  • 4 replies
  • 1582 views
  • 0 likes
  • 3 in conversation