Desktop productivity for business analysts and programmers

Transpose and calculating columns Enterprise Guide

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

Transpose and calculating columns Enterprise Guide

Hi, I have searched the forum for help for my problem, and I think i have an idea how to solve it, but I need some more help. I would really appreciate if someone out there could help me. I am new to SAS EG and have limited SAS programming skills. I do however have a good understanding of general code.

My data set is structured as follows:

Occurrence ID (unique)     Person ID     Date     Calculated column 1

x1                                   y1                   n1    

x2                                   y2                   n2    

x3                                   y1                   n3    

x4                                   y1                   n4    



I want to populate the calculated column according to how many days there are between two occurrences for a unique person.

For example:

For person ID = y1. Days between occurrence x3 and x1 = n3 - n1. The value will thereafter either set a 1, 2, 3 or 4 in the calculated column according to how many days there are.


My idea is to transpose the data set as follows:


Person ID (unique)     x1     x2     x3       x4    Calculated column x1     Calculated column x2 ...

y1                             n1     n2                n4       

y2                                              n3      

                                             


I can thereafter calculate by comparing columns x1,...xn for each person ID.


After this step, I need to transpose the data set back in order to have a unique calculated column values for each Occurrence ID.


Is this a good approach or do any of you have a better idea how I might attack this problem?


Thank you!


Accepted Solutions
Solution
‎03-02-2015 07:53 AM
Respected Advisor
Posts: 3,786

Re: Transpose and calculating columns Enterprise Guide

I don't know that you need to transpose but you will need to sort.

data person;
   input oid person:$2.  date:date9.;
  
format date date9.;
  
cards;
1 y1 27FEB2015
2 y2 27FEB2015
3 y1 01MAR2015
4 y1 05MAR2015
;;;;
   run;
proc sort data=person;
   by person date;
   run;
data persion2;
   set person;
   by person;
   dif = dif(date);
  
if first.person then call missing(dif);
   run;
proc print;
  
run;

3-2-2015 6-52-51 AM.png

View solution in original post


All Replies
Solution
‎03-02-2015 07:53 AM
Respected Advisor
Posts: 3,786

Re: Transpose and calculating columns Enterprise Guide

I don't know that you need to transpose but you will need to sort.

data person;
   input oid person:$2.  date:date9.;
  
format date date9.;
  
cards;
1 y1 27FEB2015
2 y2 27FEB2015
3 y1 01MAR2015
4 y1 05MAR2015
;;;;
   run;
proc sort data=person;
   by person date;
   run;
data persion2;
   set person;
   by person;
   dif = dif(date);
  
if first.person then call missing(dif);
   run;
proc print;
  
run;

3-2-2015 6-52-51 AM.png
New Contributor
Posts: 3

Re: Transpose and calculating columns Enterprise Guide

Thank you!

Much easier and exactly what I want to achieve.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 601 views
  • 0 likes
  • 2 in conversation