I have a a dataset where the first row is concatenated, delimited by '|'. I am using the following code to unconcatenate it. This works fine but then I want to drop patient_id which ends up deleting some patient rows. I suspect this has to do with the 'uniqueness' of the row but I am not sure. How can I unconcatenate, drop patient_id and keep all the rows?
data test;
set have;
patient=scan(patient_id, 1, '|');
variable=scan(patient_id, 2, '|');
date=scan(patient_id, 3, '|');
visit=scan(patient_id, 4, '|');
run;
have:
patiend_id Ht Wt Bm
DH-4-1|H|28SEP2017:00:00:00 55 55 55
DH-4-1|W|22SEP2017:00:00:00|A 55 55 55
DH-4-1|H|22SEP2017:00:00:00|B 55 55 55
want:
patiend_id variable Date Visit Ht Wt Bm
DH-4-1 H 28SEP2017:00:00:00 55 55 55
DH-4-1 W 22SEP2017:00:00:00 A 55 55 55
DH-4-1 H 22SEP2017:00:00:00 B 55 55 55
what I'm getting if I drop patient_id:
patiend_id variable Date Visit Ht Wt Bm
DH-4-1 W 22SEP2017:00:00:00 A 55 55 55
So how are you "dropping patient_id"? You don't show any code. Your result:
what I'm getting if I drop patient_id: patiend_id variable Date Visit Ht Wt Bm DH-4-1 W 22SEP2017:00:00:00 A 55 55 55
looks like you have done something to remove PATIENT, not patien_id
It sounds like you may be using Delete, which removes records, not variables.
If you are attempting to conditionally remove patient_id, then than isn't quite possible. A variable is either in or not in a data set. The statement would be
data test; set have; patient=scan(patient_id, 1, '|'); variable=scan(patient_id, 2, '|'); date=scan(patient_id, 3, '|'); visit=scan(patient_id, 4, '|'); drop patient_id; run;
which means that the output data set would never have the variable patient_id but keeps patient, variable, date, visit;
Note that you would be better not to call the value "date" as it has a time component and SAS makes a big difference between date and datetime values.
Also, read and treat as a datetime:
Instead of
date=scan(patient_id, 3, '|');
try
datetime = input(scan(patient_id,3,'|'),datetime.);
format datetime datetime.;
OR if all of your times are 00:00:00 because of some other sources slopping program and you actually want a DATE then
datetime = input(scan(patient_id,3,'|'),date9.);
format datetime date9.;
There are many tools in SAS to work with actual date, time or datetime values and likely anything you want to with that value will require creating a date or datetime value first, might as well do it first thing.
So how are you "dropping patient_id"? You don't show any code. Your result:
what I'm getting if I drop patient_id: patiend_id variable Date Visit Ht Wt Bm DH-4-1 W 22SEP2017:00:00:00 A 55 55 55
looks like you have done something to remove PATIENT, not patien_id
It sounds like you may be using Delete, which removes records, not variables.
If you are attempting to conditionally remove patient_id, then than isn't quite possible. A variable is either in or not in a data set. The statement would be
data test; set have; patient=scan(patient_id, 1, '|'); variable=scan(patient_id, 2, '|'); date=scan(patient_id, 3, '|'); visit=scan(patient_id, 4, '|'); drop patient_id; run;
which means that the output data set would never have the variable patient_id but keeps patient, variable, date, visit;
Note that you would be better not to call the value "date" as it has a time component and SAS makes a big difference between date and datetime values.
Also, read and treat as a datetime:
Instead of
date=scan(patient_id, 3, '|');
try
datetime = input(scan(patient_id,3,'|'),datetime.);
format datetime datetime.;
OR if all of your times are 00:00:00 because of some other sources slopping program and you actually want a DATE then
datetime = input(scan(patient_id,3,'|'),date9.);
format datetime date9.;
There are many tools in SAS to work with actual date, time or datetime values and likely anything you want to with that value will require creating a date or datetime value first, might as well do it first thing.
Try add blank into split char and use 'M' option.
data test;
set have;
patient=scan(patient_id, 1, '| ','m');
variable=scan(patient_id, 2, '| ','m');
date=scan(patient_id, 3, '| ','m');
visit=scan(patient_id, 4, '| ','m');
run;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.