combine results

[myboys2]

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?

[Reeza]

Are they already in the same file?

And is the DOB different because of a typo?

[myboys2]

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

[myboys2]

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

[Reeza]

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;

[myboys2]

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

[Reeza]

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

---

[Satish_Parida]

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;

[myboys2]

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.

[Reeza]

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?

[Satish_Parida]

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

[SuryaKiran]

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;