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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.