07-15-2016 05:44 AM - edited 07-15-2016 05:50 AM
I am trying to restructure my data and any help will be really appreciated. I have attached four screenshots,2016-07-15.png and 2016-07-15 (1).png show the original data and the rest show how I would like the data to be rearranged. I would do it manually but I have huge data and that would be time consuming
07-15-2016 05:59 AM
Your problem is not very clear. Please illustrate more so that I can give you solution.
In one png, it is the list of table columns and second one is the actual table. Now I do not understand how do you want to structure your data.
07-15-2016 06:17 AM - edited 07-15-2016 06:25 AM
Thank you for the quick reply
In the original data I have the following variables Patient Id variable, a string variable that contains a drug nme, drug recieved data, strength, repetition, patient age...etc
Patient ID Drug name recieved date strength
1 Cefrriaxone 1/1/2016 500
1 Cefrriaxone 2/1/2016 500
2 Cefaclor 5/6/2016 1000
2 Cefaclor 6/6/2016 1000
And I wanted restructure the data by transforming each different drug name in the drug name variable to a new variable and transform the date to starting date and discontinuation date (1st and last recieved date for each drug for each patient). so the data would look like this
Patient ID Ceftriaxone starting date disc.date strength cefaclor startingdate2 disc.date2 str.2
1 1 1/1/2016 2/1/2016 500 2 n/p
2 2 n/a 1 5/6/2016 6/6/2016 1000
07-15-2016 10:20 AM
I generally don't recommend this data structure. You'll need to know all the drug names for any future analysis.
If you really want it, you can do three proc transposes and merge results.
Another option is to use the macro A better way to flip
07-16-2016 03:15 AM
More data would be better. Check the MERGE skill proposed by Me,Matt,Arthur.T http://support.sas.com/resources/papers/proceedings15/2785-2015.pdf data have; input PatientID Drugname : $20. recieveddate : $20. strength; cards; 1 Cefrriaxone 1/1/2016 500 1 Cefrriaxone 2/1/2016 500 2 Cefaclor 5/6/2016 1000 2 Cefaclor 6/6/2016 1000 ; run; data temp(index=(xx=(PatientID strength))); set have; by PatientID Drugname strength; if first.strength then n=0; n+1; run; proc sql; select distinct catt('temp(where=(n=',n,'and PatientID=',PatientID,'and strength=',strength,') rename=(recieveddate=',cats(Drugname,'Date',n),'))') into : merge separated by ' ' from temp; quit; data want; merge &merge; by PatientID strength; output; call missing(of _all_); drop Drugname n; run;