topic Re: SAS Data reformat in SAS Programming

SAS Data reformat

maddepusrikanth — Fri, 14 Oct 2016 16:53:56 GMT

Hi,

need to hlep on data reformat as below.

i/p and o/p as below Excel.

SSID	Screening_Number	Date_of_Birth	date_screened	Date_Randomized	Date_Screen_failed
158018	840238004	23Dec1947	11Apr2016		11May2016
158018	840238004	23Dec1947	13Jul2016	27Jul2016
158018	840238019	23Dec1947	11Apr2016		11May2016
158018	840238019	23Dec1947	13Jul2016	27Jul2016



o/p
SSID	Screening_Number	Date_of_Birth	date_screened	Date_Screen_failed	Date_Rescreened	Date_selected
158018	840238019	23Dec1947	11Apr2016	13Jul2016	11May2016	27Jul2016

Thanks

Re: SAS Data reformat

Shmuel — Fri, 14 Oct 2016 17:03:55 GMT

It is not very clear what are the rules to get the desired output.

I seems you want one ouput record per SSID with:

- the last screening_number of SSID

- date_of_birth (may be from the last observation of SSID)

- the first date_screened of SSID

- assign last date_screened of SSID to date_screened_failed

- save the max(original date_screened_failed) to a new variable: Date_Rescreened

- save the last or max(date_randomized) to a new variable: Date_selected

You can do it using either SQL or RETAIN in a data step;

Try make your own code and attach it (or the log) if you enter difficulties.

Re: SAS Data reformat

ballardw — Fri, 14 Oct 2016 17:06:52 GMT

You really need to describe, possibly in excrutiating detail, how Date_Screen_failed ends up with the value 13Jul2016 in the output when that is not the value for that variable in the input and the other values associated with 13Jul2016 do not indicate failure in any way that I can see.

Re: SAS Data reformat

maddepusrikanth — Fri, 14 Oct 2016 17:28:27 GMT

i want max Screen_number for every SSID and if the min Date_Screened as Date_Screend dates take the Date_Screnned and MAX Date_Screened as Date_Rescreened. date screend failed & Date_Randimized.

Thanks.

Re: SAS Data reformat

Shmuel — Fri, 14 Oct 2016 21:01:44 GMT

Test next code, and change it in case I did not understand exactly what you mean:

Proc SQL;

create table want as select

SSID,

max(date_bitrh) as date_birth,

max(screen_number) as screen_number,

min(date_screened) as date_screened,

max(date_screened) as date_screened_failed,

max(date_screened_failed) as Date_Rescreened,

max(date_randomized) as Date_Selected

FROM have

GROUP by SSID;

Quit;

Re: SAS Data reformat

Ksharp — Sat, 15 Oct 2016 06:09:24 GMT

data have;
infile cards expandtabs truncover;
input (SSID	Screening_Number	Date_of_Birth	date_screened) (:$20.)	(Date_Randomized	Date_Screen_failed ) (:date9.);
new=coalesce(Date_Randomized,Date_Screen_failed);
format Date_Randomized Date_Screen_failed new date9.;
cards;
158018	840238004	23Dec1947	11Apr2016	 .	11May2016	 
158018	840238004	23Dec1947	13Jul2016	27Jul2016	 .	 
158018	840238019	23Dec1947	11Apr2016	 .	11May2016	 
158018	840238019	23Dec1947	13Jul2016	27Jul2016	 .
;
run;

proc sql;
select max(n) into : n from
(select count(*) as n from have
 group by ssid ,Screening_Number,Date_of_Birth);
quit;
proc summary data=have nway;
 class ssid Screening_Number	Date_of_Birth;
 output out=want idgroup(out[&n] (date_screened new)=);
run;