BookmarkSubscribeRSS Feed
myboys2
Fluorite | Level 6

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?

 

11 REPLIES 11
Reeza
Super User

Are they already in the same file?

And is the DOB different because of a typo?

myboys2
Fluorite | Level 6
yes type DOB is the same and yes all on one file
myboys2
Fluorite | Level 6

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

Reeza
Super User

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
Fluorite | Level 6

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
Super User

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
Lapis Lazuli | Level 10

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
Fluorite | Level 6

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
Super User

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
Lapis Lazuli | Level 10
Please post the whole query and the table datatypes which are involved in any type of calculation for this.
SuryaKiran
Meteorite | Level 14

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1233 views
  • 0 likes
  • 4 in conversation