Unconcatenate column while keeping dataset sas

[serena13lee]

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

[Reeza]

Is this from a text file? How did you originally read it into SAS?

[ballardw · Accepted solution]

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.

[Ksharp]

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;