BookmarkSubscribeRSS Feed
gobejo
Calcite | Level 5

Hi,

I have data that looks like this:

IDpartyfilingdtcount
2115389bankoriginal2012-09-131
2115389bankcontinuation2011-10-062
2115389bankcorrection2008-04-093

             

I want the data to look like this:

IDpartyfilingdtfilingdtfilingdt
2115389bankoriginal2012-09-13continuation2011-10-06correction2008-04-09
9 REPLIES 9
Reeza
Super User

Note that variable names in SAS need to be unique.

SAS Learning Module: Reshaping data long to wide using the data step

Jagadishkatam
Amethyst | Level 16

Hi,

Please try the below code.

As Reeza rightly suggested the variable names must be unique. However an alternative way is to display the variable names with labels, we could assign the same name as variable label. so the variable names will be different but the labels will be same. Hope it meets your requirement.

data have;

input ID    party$    filing :$20.    dt:yymmdd10.    count;

format dt yymmdd10.;

cards;

2115389    bank    continuation    2011-10-06    2

2115389    bank    correction    2008-04-09    3

2115389    bank    original    2012-09-13    1

;

proc sort data=have;

by id party;

run;

data arays;

retain id party filing1 dt1 filing2 dt2 filing3 dt3;

set have;

array vs(3) $20 filing1 - filing3;

array sv(3) dt1 - dt3;

retain filing1 - filing3 dt1 - dt3;

by id party;

if first.party then do;

do i = 1 to 3;

vs(i)='';

sv(i)=.;

end;

end;

vs(count)=filing;

sv(count)=dt;

format dt1 - dt3 yymmdd10.;

label filing1 ='filing'

      filing2 ='filing'

      filing3 ='filing'

      dt1     ='dt'

      dt2     ='dt'

      dt3     ='dt';

      if last.party;

      keep id party filing1-filing3 dt1-dt3;

run;

proc print data=arays label;

var _all_;

run;

Thanks,

Jag

Thanks,
Jag
gobejo
Calcite | Level 5

Hi Jag,

The requirement changed, and modified the following code you gave me.

Data work.ucc_transpose;

LENGTH name_1 date_1 type_1 name_2 date_2 type_2 name_3 date_3 type_3;

SET founders.ucc_count;

array a(3) $50 name_1 - name_3;

array b(3) $20 type_1 - type_3;

array c(3) date_1 - date_3;

retain name_1 - name_3 type_1 - type_3 date_1 - date_3;

by duns_number;

if first.duns_number then do;

do i = 1 to 3;

a(i)='';

b(i)='';

c(i)=.;

end;

end;

a(count)=name;

b(count)=type;

c(count)=date;

format dt1 - dt3 yymmdd10.;

label type_1 ='filing type 1'

      type_2 ='filing type 2'

      type_3 ='filing type 3'

      date_1 ='UCC Date 1'

      date_2 ='UCC Date 2'

      date_3 ='UCC Date 3';

      if last.duns_number;

      keep id secured_party_name name_1-name_3 type_1-type_3 date_1-date_3;

run;

and this is what the error log showed:

Data work.ucc_transpose;
26         LENGTH name_1 dt_1 type_1 name_2 dt_2 type_2 name_3 dt_3 type_3;
                                                                          _
                                                                          22
1          1
           _
           352
ERROR 22-322: Expecting a numeric constant. 

ERROR 352-185: The length of numeric variables is 3-8.

27         SET founders.ucc_count;
28         array a(3) $50 name_1 - name_3;
29         array b(3) $20 type_1 - type_3;
30         array c(3) dt_1 - dt_3;
31         retain name_1 - name_3 type_1 - type_3 dt_1 - dt_3;
32         by duns_number secured_party_name;
33         if first.duns_number then do;
34         do i = 1 to 3;
35         a(i)='';
36         b(i)='';
37         c(i)=.;
38         end;
39         end;
40         a(count)=name;
41         b(count)=type;
42         c(count)=date;
43         format dt1 - dt3 yymmdd10.;
44         label type_1 ='filing type 1'
45               type_2 ='filing type 2'
2                                                          The SAS System                               08:53 Tuesday, March 3, 2015

46               type_3 ='filing type 3'
47               dt_1 ='UCC Date 1'
48               dt_2 ='UCC Date 2'
49               dt_3 ='UCC Date 3';
50               if last.duns_number;
51               keep id secured_party_name name_1-name_3 type_1-type_3 date_1-date_3;
52         run;

Jagadishkatam
Amethyst | Level 16

Please replace LENGTH with RETAIN to avoid the error.

Thanks,
Jag
gobejo
Calcite | Level 5

It says the variablesis uninitialized:

proc sort data=founders.ucc_count;
26         by duns_number secured_party_name;
27         run;

NOTE: There were 291 observations read from the data set FOUNDERS.UCC_COUNT.
NOTE: The data set FOUNDERS.UCC_COUNT has 291 observations and 5 variables.
NOTE: PROCEDURE SORT used (Total process time):
      real time           0.00 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              503.37k
      OS Memory           16536.00k
      Timestamp           03/03/2015 10:12:49 AM
      Step Count                        34  Switch Count  31
      Page Faults                       0
      Page Reclaims                     60
      Page Swaps                        0
      Voluntary Context Switches        106
      Involuntary Context Switches      46
      Block Input Operations            0
      Block Output Operations           256
     

28        
29        
30        
31         Data work.ucc_transpose;
32         Retain name_1 dt_1 type_1 name_2 dt_2 type_2 name_3 dt_3 type_3;
33         SET founders.ucc_count;
34         array a(3) $50 name_1 - name_3;
2                                                          The SAS System                               08:53 Tuesday, March 3, 2015

35         array b(3) $20 type_1 - type_3;
36         array c(3) dt_1 - dt_3;
37         retain name_1 - name_3 type_1 - type_3 dt_1 - dt_3;
38         by duns_number secured_party_name;
39         if first.duns_number then do;
40         do i = 1 to 3;
41         a(i)='';
42         b(i)='';
43         c(i)=.;
44         end;
45         end;
46         a(count)=name;
47         b(count)=type;
48         c(count)=date;
49         format dt1 - dt3 yymmdd10.;
50         label type_1 ='filing type 1'
51               type_2 ='filing type 2'
52               type_3 ='filing type 3'
53               dt_1 ='UCC Date 1'
54               dt_2 ='UCC Date 2'
55               dt_3 ='UCC Date 3';
56               if last.duns_number;
57               keep id secured_party_name name_1-name_3 type_1-type_3 date_1-date_3;
58         run;

NOTE: Variable name is uninitialized.
NOTE: Variable type is uninitialized.
NOTE: Variable date is uninitialized.
NOTE: Variable dt1 is uninitialized.
NOTE: Variable dt2 is uninitialized.
NOTE: Variable dt3 is uninitialized.

Thanks

Jagadishkatam
Amethyst | Level 16

I think you do not have the name, type and date variables in the dataset you are using in the set statement. could you please provide the proc contents data to me .

please run

proc contents data=founders.ucc_count;

run;

it will help us to see which variables are there in your dataset.

Thanks,
Jag
TomKari
Onyx | Level 15

Another approach:

data Inter1;
length Column $8 Value $32;
keep ID party Column Value;
set Have;
Column = "dt"||strip(put(Count,best5.));
Value = put(dt, date.);
output;
Column = "filing"||strip(put(Count,best5.));
Value = filing;
output;
run;

proc transpose data=Inter1 out=Want(drop=_:);
by ID party;
id Column;
var Value;
run;

art297
Opal | Level 21

With the macro you can download from: http://www.sascommunity.org/wiki/A_Better_Way_to_Flip_(Transpose)_a_SAS_Dataset

You can transpose your data with the following call:

%transpose(data=have, out=want, by=id party, id=count, var=filing dt)

and, if you have a large file, it will run quite a bit faster than using proc transpose.

gobejo
Calcite | Level 5

Hi Arthur,

I do not have access to download or install anything.

Thanks,

Tom

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1172 views
  • 0 likes
  • 5 in conversation