SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Data restructuring

Reply
New Contributor
Posts: 3

Data restructuring

[ Edited ]

Hi all

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 

 


2016-07-15 (1).png2016-07-15 (2).png2016-07-15 (3).png2016-07-15.png
Super Contributor
Posts: 266

Re: Data restructuring

Posted in reply to walidqerem

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.

 

 

New Contributor
Posts: 3

Re: Data restructuring

[ Edited ]

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

Example

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

Super User
Posts: 19,877

Re: Data restructuring

Posted in reply to walidqerem

How many drugs do you have?

New Contributor
Posts: 3

Re: Data restructuring

34
Super User
Posts: 19,877

Re: Data restructuring

Posted in reply to walidqerem

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

http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

Super User
Posts: 10,046

Re: Data restructuring

Posted in reply to walidqerem
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;

Ask a Question
Discussion stats
  • 6 replies
  • 516 views
  • 0 likes
  • 4 in conversation