topic Re: Code Unintentionally Causes Data to be Omitted. How to Fix? in SAS Programming

Code Unintentionally Causes Data to be Omitted. How to Fix?

acemanhattan — Mon, 06 Jul 2015 15:52:34 GMT

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.

Re: Code Unintentionally Causes Data to be Omitted. How to Fix?

SASKiwi — Mon, 06 Jul 2015 19:47:16 GMT

Add the "Referred To" variable to your KEEP statement.

Re: Code Unintentionally Causes Data to be Omitted. How to Fix?

Reeza — Mon, 06 Jul 2015 20:30:59 GMT

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.

Re: Code Unintentionally Causes Data to be Omitted. How to Fix?

Ksharp — Tue, 07 Jul 2015 12:34:21 GMT

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

Re: Code Unintentionally Causes Data to be Omitted. How to Fix?

acemanhattan — Fri, 10 Jul 2015 15:11:14 GMT

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.