Transposing a long dataset to wide with multiple variable types

[NandiniA]

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!

[Tom]

It looks like you currently have the data stored in a single character variable named RESPONSE.

You appear to also have a variableTARGET_TYPE that appears to indicates what TYPE of variable needs to be created to store this particular RESPONSE. 

What variable has the NAME that you want to use for the new variable?

What are the variables that uniquely identify the set of observations you want to transpose into one?

Do you need code that works for this particular dataset, with its specific set of variables?

If so then just write some code that that reads in all of the observations for the output observation in one iteration of the data step.

So something like:

data want;
  do until (last.id);
    set have;
    by id;
    if NAME='DATE' then date=input(left(response),anydtdte.);
    else if NAME='AGE' then age=input(left(response),32.);
    ...
  end;
  drop NAME TARGET_TYPE RESPONSE ;
run;

Or do you want a program that could convert ANY dataset?  If so then select the unique combinations of NAME and TARGET_TYPE from the dataset and use that to generate a program like the one above.

For a more detailed answer provide a small example input dataset (as a working DATA step).  Also provide a data step that creates the dataset you want to get from that example input.

[Tom]

Since you are using this BY statement in your PROC TRANPOSE

by SID FormDateTime FirstName MiddleName LastName DOB;

To get duplicate observations you would need to have changes in one of those variables.  Are you sure that FORMDATETIME is the same of all observations you want to combine? What about the other 5 varaibles?

Or are you just talking about the fact that you generated TWO datasets, but did not include a step to combine them?

data want;
  merge wide_num wide_char;
  by SID FormDateTime FirstName MiddleName LastName DOB;
run;

Also you will need to apply the FORMAT to the variables AFTEr the PROC TRANSPOSE step.

For example both DATE and TIME variables are numeric, so I would assume you put them into the WIDE_NUM dataset.  But if you do not attach a FORMAT to the resulting variable(s) then the values will be hard for humans to interpret.