DATA Step, Macro, Functions and more

combine results

Reply
Contributor
Posts: 30

combine results

[ Edited ]

hi - I have a file coming in that has two records the same expect the test name and value

 

 

first name   last name  ssn                 date of service    dob               test  name              value

 

john            doe           123456789      2017-01-01        1967-12-01    height(feet)               4   

john           doe            123456789       2017-01-01        1967-12-01     height(inches)           11.5

 

I need to make these two records into one but the value should be a final

result as 4 feet 11.5 inches (59.5 inches  [(4*12)+11.5])

 

how would I do this?

 

Super User
Posts: 23,224

Re: combine results

Are they already in the same file?

And is the DOB different because of a typo?

Contributor
Posts: 30

Re: combine results

yes type DOB is the same and yes all on one file
Contributor
Posts: 30

Re: combine results

sorry typo DOB is the same on both records and yes they are on the same file

Super User
Posts: 23,224

Re: combine results

Something like the following is what you want:

Use RETAIN to keep the value across the rows and set it to the variable feet. This assumes the data is always in the order you've shown.

 

data want;
set have;
by ssn;
retain feet;

if first.ssn then feet=value;
else if last.ssn then do;
    height = Feet*12+value;
    output;
end;
run;
Contributor
Posts: 30

Re: combine results

Thank you Reeza but I should probably give you more info

 

On the file there are multiple test names so one ssn can have different test names.  For example

 

 

ssn              name         test name     value

123456789 john smith  height(feet)      4

123456789 john smith  height(inches)  11.5

123456789 john smith  BMI                  20

 

I just need to make  the two records in one value.....so one line with 4 feet and 11.5 converted into height in inches

 

Hope this makes sense - thank you

Super User
Posts: 23,224

Re: combine results

A SQL join is likely a better option in this case. 

 

Or you can use the LAG() to figure out which are height records, but again, you need to make sure that the data is always in the same order.

 

You should try and adapt it and I can help if you run into issues. 

 


myboys2 wrote:

Thank you Reeza but I should probably give you more info

 

On the file there are multiple test names so one ssn can have different test names.  For example

 

 

ssn              name         test name     value

123456789 john smith  height(feet)      4

123456789 john smith  height(inches)  11.5

123456789 john smith  BMI                  20

 

I just need to make  the two records in one value.....so one line with 4 feet and 11.5 converted into height in inches

 

Hope this makes sense - thank you


 

Frequent Contributor
Posts: 109

Re: combine results

This works, let us know if it worked for you.

 

data have;
infile datalines dlm=',' dsd;
input @1 ssn:$9. @11 name:$11. @23 test_name:$17. @41 value:best12.;
datalines;
123456789 john smith  height(feet)      4
123456789 john smith  height(inches)    11.5
123456789 john smith  BMI               20
123456788 john doe    height(feet)      5
123456788 john doe    height(inches)    12
123456788 john doe    BMI               25
;
run;

proc sql;
create table want as
select ssn, name, test_name, value from have 
where test_name not in('height(feet)','height(inches)')
union
select a.ssn, a.name,'height(feet)' as test_name, (12*a.value+b.value) as value
from (select ssn, name, test_name, value
      from have where test_name='height(feet)') as a,
	 (select ssn, name, test_name, value
      from have where test_name='height(inches)') as b
where a.ssn=b.ssn
;
quit;
Contributor
Posts: 30

Re: combine results

Posted in reply to Satish_Parida

Thank you but it is not giving me another testname with height and feet combined. so I am looking for the two records of 4 feet and 11.5 inches to be on one line converted to inches.  so maybe test name "height"  59.5 inches  (4*12+11.5)

 

I also had to convert the value to numeric since the calc will not work until numeric.

Super User
Posts: 23,224

Re: combine results

Does the demo code work? Other than the name still being height (inches) it's correct. In fact, height inches is correct becuase it is height in inches....if you want it named differently though, just change the name in the UNION statement - ie height(feet) -> change to what you want it to be called.

 

select a.ssn, a.name,'height(feet)' as test_name, (12*a.value+b.value) as value

 

If it works for the sample data but not your data, how is your data different?

 

 

Frequent Contributor
Posts: 109

Re: combine results

Please post the whole query and the table datatypes which are involved in any type of calculation for this.
Valued Guide
Posts: 556

Re: combine results

Try this

 

data have;
infile datalines dlm=' ';
input ssn $ 1-10 name $ 11-22 test_name $ 23-38 @41 value ;
datalines;
123456789 john smith  height(feet)      4
123456789 john smith  height(inches)    11.5
123456789 john smith  BMI               20
123456788 john doe    height(feet)      5
123456788 john doe    height(inches)    12
123456788 john doe    BMI               25
;
run;
PROC SORT DATA=Have ;
by SSN Name test_name;
run;
DATA Want(Drop=Height);
retain Height;
SET HAVE;
by SSN Name test_name;
IF First.SSN then Height=0;
IF test_name='height(feet)' then DO;
	Height=value*12;
	Delete;
	end;
IF test_name='height(inches)' then DO;
	Value=Height+Value;
	test_name='height';
	end;
Run;
Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 11 replies
  • 158 views
  • 0 likes
  • 4 in conversation