I have this dataset:
Provider Referred To Percent
104TH AVENUE MEDICAL CLINIC | VALLEY MEDICAL CENTER RENTON | 0.1682752732 | 1 |
104TH AVENUE MEDICAL CLINIC | FEDERAL WAY MEDICAL INVESTOR | 0.1309905444 | 2 |
104TH AVENUE MEDICAL CLINIC | 104TH AVENUE MEDICAL CLINIC | 0.0862805036 | 3 |
2BWELL | MARK YERBY | 0.3450408963 | 1 |
2BWELL | NORTHWEST ENDOCRINOLOGY LLC | 0.2503723051 | 2 |
2BWELL | JAY PAUL DOUGLASS MD LLC | 0.2152037941 | 3 |
4TH STREET MEDICAL CARE | PROVIDENCE EVERETT | 0.6061960799 | 1 |
4TH STREET MEDICAL CARE | WESTERN WASHINGTON MED GROUP | 0.1132793836 | 2 |
4TH STREET MEDICAL CARE | 4TH STREET MEDICAL CARE | 0.0571218555 | 3 |
This code produces the following dataset
DATA data.top_3;
SET data.top_3_allwd; BY attributed_tin_nm_rllp;
* Tells SAS not to reset these variables to
missing when going to top of datastep;
RETAIN first second third percent1 percent2 percent3;
* Set variables to missing when reading new
member - clear previous member's data!;
if FIRST.attributed_tin_nm_rllp = 1 then do;
first=.; second=.; third=.;
percent1=.; percent2=.; percent3=.;
end;
if count = 1 then do ;
first = prov_name_serv; percent1= percent_of_allwd;
end;
if count = 2 then do ;
second = prov_name_serv; percent2= percent_of_allwd;
end;
if count = 3 then do ;
third = prov_name_serv; percent3= percent_of_allwd;
end;
* Output variables only when done with member;
if LAST.attributed_tin_nm_rllp = 1 then OUTPUT;
KEEP attributed_tin_nm_rllp first second third percent1 percent2 percent3;
RUN;
Provider 1st 2nd 3rd 1st % 2nd% 3rd%
104TH AVENUE MEDICAL CLINIC | . | . | . | 0.1682752732 | 0.1309905444 | 0.0862805036 |
2BWELL | . | . | . | 0.3450408963 | 0.2503723051 | 0.2152037941 |
4TH STREET MEDICAL CARE | . | . | . | 0.6061960799 | 0.1132793836 | 0.0571218555 |
The problem is that the name of the referred to clinics gets omitted, but I can't see why.
Add the "Referred To" variable to your KEEP statement.
Which Referred to variable are looking to keep, all three or the first/last?
Assuming you're trying to flip your data you should look into proc transpose or arrays:
SAS Learning Module: Reshaping data long to wide using the data step
I think has a macro that does multiple columns at once as well.
You mean Arthur.T ? We don't see him for a long time at this forum. Want know where he is .
A Better Way to Flip (Transpose) a SAS Dataset - sasCommunity
The problem was with this part of the code:
if FIRST.attributed_tin_nm_rllp = 1 then do;
first=.; second=.; third=.;
it was fixed when I changed to
if FIRST.attributed_tin_nm_rllp = 1 then do;
first=' '; second=' '; third=' ';
and preceded this with
FORMAT first second third $CHAR105.;
Thanks for the responses.
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.
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.