BookmarkSubscribeRSS Feed
Satori
Quartz | Level 8

I'm running a macro to separate unique and multiple entries by id. For multiple entries, I want to keep the max (for the list of variables fvar) and drop the other ones. Then I merge this subset with the set of multiple entries so that I can recover/include the variables not specified in fvar but that are in the multiple entries set. In the end, I append all unique and multiple(now also unique) subsets. Here is my code below.

 

%let fvar = XFIAS XIFAS XTFAS XOFAS XCUAS XSTOK XDEBT XOCAS XCASH XASSTS XSHFD XCAPI XOSFD XNCLI XLTDB XONCL XPROV XCULI XLOAN XCRED XOCLI XWKCA XNCAS XENVA XEMPL XREV XSLS XCOST XGROS XOOPE XOPPL XFIRE XFIEX XFIPL XPLBT XTAXA XPLAT XEXRE XEXEX XEXTR XPL XEXPT XMATE XSTAF XDEPR XOOPI XINTE XRD XCF XAV XEBTA ;

option mprint symbolgen;
%macro cc(dsin,dsout);
%do y=1990 %to 2022;

data bv_&y; set &dsin; if FYEAR=&y; proc sort data=bv_&y nouniquekey uniqueout=bvu_&y; by ID;

data bv_&y; set &dsin; if FYEAR=&y; proc sort data=bv_&y nouniquekey out=bvm_&y; by ID;
proc summary data=bvm_&y nway; class ID; var &fvar.; id FYEAR; output out=bvm1_&y max=;
data bvm_&y; merge bvm1_&y(IN=bvm1_&y) bvm_&y; by ID FYEAR; if bvm1_&y=1;

%end;

data &dsout; set bvu_1990-bvu_2022 bvm_1990-bvm_2022; run;
data &dsout; set &dsout; drop _FREQ_ _TYPE_ i j;

%mend;

%cc(b_us,data.b_us)
%cc(b_dc,data.b_dc)
option nomprint nosymbolgen;

 

My problem is that in the end I still have repeated entries for the same id and fyear. Is there a way to fix this?

3 REPLIES 3
Tom
Super User Tom
Super User

I do not understand your description, or the code.

 

If you want replace values with the MAX  in the BY groups with multiple observations then why not just replace the the values with the MAX in ALL of the by groups?  The MAX of a single number is just that number.

andreas_lds
Jade | Level 19

This thread seems to be the continuation of https://communities.sas.com/t5/SAS-Programming/running-macro/m-p/892565.

@Satori : Please post data in usable form and show the expected output. I am not sure that i understand what you are trying to achieve at all.

PaigeMiller
Diamond | Level 26

@Satori 

 

this is your third thread on this topic. Please in the future use one thread.

 

In those other threads, people (including me) have asked to see a portion of your data, and people have asked you to explain further, because no one understands what you want.

 

You have not provided data, you have not provided a more complete explanation, and this is not a good way to get help. This is a good way to get nowhere (and apparently that is what has happened so far)

 

Please provide the data as working SAS data step code. Please provide a more detailed and complete explanation.

 

--
Paige Miller

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