topic Re: Restore the order of mismatched observations in New SAS User

Restore the order of mismatched observations

sarahzhou — Fri, 15 Oct 2021 10:26:00 GMT

Hi,

I am new to SAS, I need help from you.

Is there a way to restore the order of mismatched observations?

Consider the following csv file,

PERSON_ID<|>DEPT_ID<|>DATE_JOINED
AAAAA<|>S1<|>2021
/01/03
BBBB
BB<|>S2<|>2021/02/03
CCCCC<|>S1<|>2021/03/05

I wish the output like,

Thank you!

Re: Restore the order of mismatched observations

PeterClemmensen — Fri, 15 Oct 2021 10:28:14 GMT

How 'messed up' is the file? Are any delimiters missing ?

Re: Restore the order of mismatched observations

Ksharp — Fri, 15 Oct 2021 12:42:47 GMT

It is really uneasy.

You need post real data to get pattern of starting a new line .

data have;
infile "c:\temp\temp.csv" lrecl=3000 length=len;
input have $varying3000. len;
if _n_=1 then delete;
if prxmatch('/^[a-z]{4,}/i',have) then group+1;
run;


data want;
 do until(last.group);
   set have;
   by group;
   length want $ 3000;
   want=cats(want,have);
 end;

length PERSON_ID DEPT_ID DATE_JOINED $ 100;
PERSON_ID=scan(want,1,'<|>');
DEPT_ID=scan(want,2,'<|>');
DATE_JOINED=scan(want,3,'<|>');
 drop have;
 run;

Re: Restore the order of mismatched observations

sarahzhou — Mon, 18 Oct 2021 02:57:34 GMT

Thanks!