Hi,
I have a dataset with several variable types (char, num, date, time, checkboxes, string) and I am trying to convert it to a wide format with 1 row per ID. I have created RESP_C, RESP_N, RESP_DATE and RESP_TIME variables to assign the correct the appropriate values to each of these columns.
data prepared;
set joined;length RESP_C $1000 RESP_N 8 RESP_DATE 12 RESP_TIME 8;
RESP_C = strip(Response);
select (upcase(Target_Type));
when ('DATE') do;
RESP_DATE = input(strip(Response), anydtdte.);
format RESP_DATE date9.;
RESP_C = "";
end;
when ('TIME') do;
RESP_TIME = input(strip(Response), anydttme.); /* NA 4/29: timepart is for mixed reponse, so removed it (date + time) */
format RESP_TIME timeampm.;
RESP_C = "";
end;
when ('CHECK') do;
if strip(Response) in ('Y','YES','TRUE','1','CHECKED','Selected') then RESP_N = 1; /* NA 4/29: Added 'Selected' to the list as that is the response of the form */
else if missing(Response) then RESP_N = .;
else RESP_N = 0;
/*format RESP_N 1.; */
RESP_C = "";
end;
when ('NUM') do;
RESP_N = input(strip(Response), best32.);
RESP_C = "";
end;
otherwise do; /* CHAR */
/* keep RESP_C */
end;
end;
run;
proc sort data=prepared;
by SID FormDateTime FirstName MiddleName LastName DOB;
run;
/***********************************************************************
* 7) Transpose into wide:
* - Character targets (CHAR) -> wide_char
* - Numeric targets (DATE, TIME, NUM, CHECK) -> wide_num
***********************************************************************/
proc transpose data=prepared(where=(Target_Type='CHAR'))
out=wide_char(drop=_name_);
by SID FormDateTime FirstName MiddleName LastName DOB;
id VarName_ID;
var RESP_C;
run;
proc transpose data=prepared(where=(Target_Type in ('DATE','TIME','NUM','CHECK')))
out=wide_num(drop=_name_);
format RESP_DATE date9. RESP_TIME timeampm.;
by SID FormDateTime FirstName MiddleName LastName DOB;
id VarName_ID;
var RESP_N RESP_DATE RESP_TIME;
run;
However, this is only working on some date values in the dataset and not applying uniformly to all the values. Additionally, after transposing each variable type is a unique row and not one row per ID.
I would appreciate any help in fixing this code. Thank you!
