1

I have a sas datebase with something like this:

id birthday Date1    Date2
1  12/4/01  12/4/13  12/3/14
2  12/3/01  12/6/13  12/2/14
3  12/9/01  12/4/03  12/9/14
4  12/8/13  12/3/14  12/10/16

And I want the data in this form:

id Date     Datetype
1  12/4/01  birthday  
1  12/4/13  1   
1  12/3/14  2    
2  12/3/01  birthday  
2  12/6/13  1
2  12/2/14  2
3  12/9/01  birthday
3  12/4/03  1
3  12/9/14  2
4  12/8/13  birthday
4  12/3/14  1
4  12/10/16 2

Thanks by ur help, i'm on my second week using sas <3 Edit: thanks by remain me that i was not finding a sorting method.

sfrancov
  • 21
  • 2
  • 1
    Make an attempt and show what you have tried. You will learn more if you have a go, and someone will be more likely to assist you. – Alan Sep 03 '18 at 03:12
  • This isn't a sort, it's a transpose. You can try PROC TRANSPOSE or use an array method. Both will work for your data https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/ – Reeza Sep 03 '18 at 03:26

2 Answers2

0

Good day. The following should be what you are after. I did not come up with an easy way to rename the columns as they are not in beginning data. 

   /*Data generation for ease of testing*/
    data begin; 
        input id birthday $ Date1 $ Date2 $; 
        cards;
    1  12/4/01  12/4/13  12/3/14
    2  12/3/01  12/6/13  12/2/14
    3  12/9/01  12/4/03  12/9/14
    4  12/8/13  12/3/14  12/10/16
    ; run; 

   /*The trick here is to use date: The colon means everything beginning with date, comparae with sql 'date%'*/
    proc transpose data= begin out=trans; 
        by id; 
        var birthday date: ; 
    run;

    /*Cleanup. Renaming the columns as you wanted.*/
    data trans;
        set trans;
        rename _NAME_= Datetype COL1= Date;
    run;

See more from Kent University site

pinegulf
  • 1,334
  • 13
  • 32
  • To clean up the names you can add a single line to the clean up step. `if _name_ in ('Date1', 'Date2') then _name_ = compress(_name_, , 'kd');` – Reeza Sep 03 '18 at 18:14
0

Two steps

  • Pivot the data using Proc TRANSPOSE.
  • Change the names of the output columns and their labels with PROC DATASETS

Sample code

proc transpose 
  data=have 
  out=want
    ( keep=id _label_ col1)
  ;
  by id;
  var birthday date1 date2;
  label birthday='birthday' date1='1' date2='2' ; * Trick to force values seen in pivot;
run;

proc datasets noprint lib=work;
  modify want;
  rename 
    _label_ = Datetype
    col1 = Date
  ;
  label
    Datetype = 'Datetype'
  ;
run;

The column order in the TRANSPOSE output table is:

  • id variables
  • copy variables
  • _name_ and _label_
  • data based column names

The sample 'want' shows the data named columns before the _label_ / _name_ columns. The only way to change the underlying column order is to rewrite the data set. You can change how that order is perceived when viewed is by using an additional data view, or an output Proc that allows you to specify the specific order desired.

Richard
  • 25,390
  • 3
  • 25
  • 38
  • Note that PROC TRANSPOSE does NOT create a \_LABEL_ variable if none of the variables being transposed have labels attached to them. It will always create the \_NAME_ variable, although there are options to change the name that it uses for that variable. – Tom Sep 04 '18 at 12:44
  • Good point. The sample code forces labels in the `TRANSPOSE` step to ensure the pivot has the desired transform values in a column named _label_. Without the label statement, things would depend on the incoming data set. – Richard Sep 04 '18 at 13:06