topic Re: combine results in SAS Programming

combine results

myboys2 — Wed, 14 Feb 2018 19:18:53 GMT

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?

Re: combine results

Reeza — Wed, 14 Feb 2018 19:12:41 GMT

Are they already in the same file?

And is the DOB different because of a typo?

Re: combine results

myboys2 — Wed, 14 Feb 2018 19:15:45 GMT

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

Re: combine results

myboys2 — Wed, 14 Feb 2018 19:16:55 GMT

yes type DOB is the same and yes all on one file

Re: combine results

Reeza — Wed, 14 Feb 2018 19:27:18 GMT

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;

Re: combine results

myboys2 — Tue, 27 Feb 2018 17:49:35 GMT

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

Re: combine results

Reeza — Tue, 27 Feb 2018 17:51:20 GMT

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

Re: combine results

Satish_Parida — Tue, 27 Feb 2018 18:50:30 GMT

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;

Re: combine results

myboys2 — Tue, 27 Feb 2018 19:49:27 GMT

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.

Re: combine results

Reeza — Tue, 27 Feb 2018 20:32:14 GMT

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?

Re: combine results

SuryaKiran — Tue, 27 Feb 2018 20:40:42 GMT

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;

Re: combine results

Satish_Parida — Wed, 28 Feb 2018 08:29:14 GMT

Please post the whole query and the table datatypes which are involved in any type of calculation for this.