How to concert the following dataset to stacked format? Sample file attached.

Reply
Occasional Contributor
Posts: 5

How to concert the following dataset to stacked format? Sample file attached.

NameCHINA NTH.ET.PTL.HDG. DEAD - DELIST.05/04/13CHINA NORTH EAST - TOTAL ASSETSCHINA NORTH EAST - CASH DIVIDENDS PAID - TOTALXYRATEXXYRATEX LTD. - TOTAL ASSETSXYRATEX LTD. - CASH DIVIDENDS PAID - TOTALCNOOC SPN.ADR 1:100CNOOC LTD - TOTAL ASSETSCNOOC LTD - CASH DIVIDENDS PAID - TOTALCHINAWE.COMCHINAWE.COM - TOTAL ASSETSCHINAWE.COM - CASH DIVIDENDS PAID - TOTAL
Code26492K(P)26492K(WC02999)26492K(WC04551)29060T(P)29060T(WC02999)29060T(WC04551)13596P(P)13596P(WC02999)13596P(WC04551)879632(P)879632(WC02999)879632(WC04551)
15-04-20091.5211019202.273479810116.813548751220752790.0121520
16-04-20091.511019202.263479810114.423548751220752790.0121520
17-04-20091.511019202.434798101193548751220752790.0121520
20-04-20091.511019202.463479810112.653548751220752790.0121520
21-04-20091.4811019202.473479810113.073548751220752790.0121520
22-04-20091.4711019202.933479810108.123548751220752790.0121520
23-04-20091.5211019203.053479810112.153548751220752790.0121520
24-04-20091.5411019203.343479810115.553548751220752790.0121520
27-04-20091.7111019203.33479810110.323548751220752790.0121520
28-04-20091.811019203.33479810108.953548751220752790.0121520
29-04-20091.911019203.393479810112.753548751220752790.0121520
30-04-20092.0811019203.353479810111.353548751220752790.0121520
01-05-20092.1511019203.543479810114.553548751220752790.0121520
04-05-20092.5511019203.533479810126.563548751220752790.0121520
05-05-20092.4911019203.5534798101243548751220752790.0121520
06-05-20092.7911019203.533479810127.463548751220752790.0121520
07-05-20092.9511019203.363479810122.743548751220752790.0121520
08-05-20092.9911019203.463479810130.43548751220752790.0121520
11-05-2009311019203.353479810124.973548751220752790.0121520
12-05-20092.9911019203.383479810124.963548751220752790.0121520
13-05-20092.8511019203.33479810127.313548751220752790.0121520
14-05-20092.711019203.343479810125.073548751220752790.0121520
15-05-20092.8711019203.373479810123.773548751220752790.0121520
18-05-20092.9311019203.463479810129.863548751220752790.0121520
19-05-20092.9311019203.563479810133.223548751220752790.0121520
20-05-20092.911019203.463479810132.473548751220752790.0121520
21-05-20092.8611019203.423479810128.913548751220752790.0121520
22-05-20092.9911019203.453479810126.783548751220752790.0121520
25-05-20092.9911019203.453479810126.783548751220752790.0121520
26-05-20092.9711019203.63479810123.563548751220752790.00521520
27-05-20092.9411019203.453479810124.813548751220752790.00521520
28-05-20093.5711019203.333479810126.473548751220752790.00521520
29-05-20093.8911019203.33479810134.023548751220752790.00521520
01-06-20094.6211019203.573479810143.933548751220752790.00521520
02-06-20094.5711019203.53479810141.863548751220752790.00521520
03-06-20094.2311019203.63479810136.473548751220752790.00521520
04-06-20094.3611019203.763479810141.073548751220752790.00521520
05-06-20094.311019203.923479810142.433548751220752790.00521520
08-06-20094.3511019204.113479810139.863548751220752790.00521520
09-06-20094.3611019204.3934798101383548751220752790.00521520
10-06-20094.6411019204.43479810139.773548751220752790.00521520
11-06-20095.0911019204.433479810141.713548751220752790.00521520
12-06-20095.2911019204.373479810139.763548751220752790.00521520
15-06-20095.8211019204.313479810133.33548751220752790.00521520
16-06-20095.2811019204.043479810126.083548751220752790.00421520
17-06-20094.3711019203.833479810123.933548751220752790.00421520
18-06-20093.9511019203.83479810124.763548751220752790.00421520
19-06-20094.5211019203.853479810124.013548751220752790.00421520
22-06-20093.911019203.73479810119.463548751220752790.00421520
23-06-20093.7411019203.733479810119.383548751220752790.00421520
24-06-20094.2511019203.953479810122.823548751220752790.00421520
25-06-20094.6211019204.63479810126.183548751220752790.00421520
26-06-20094.5911019204.963479810126.253548751220752790.00421520
29-06-20094.5411019204.983479810126.163548751220752790.00421520
30-06-20094.44110192053479810123.033548751220752790.00421520
Attachment
Occasional Contributor
Posts: 5

Re: How to concert the following dataset to stacked format?

In real data, there are much more companies than listed above and the time period is much longer.

Thank you a lot!

Respected Advisor
Posts: 3,887

Re: How to concert the following dataset to stacked format?

I would split this up into a transaction table and 2 reference tables.

Transaction table:

{Company_Code, Payment_Type_Code, Date, Amount}

Reference tables:

{Company_Code, Company_Name}

{Payment_Type_Code, Payment_Type_Name}

You then could use the reference tables to create a format (Proc Format with CNTLIN) and then apply this formats to the appropriate columns in your transaction table.

If you need actual code then I suggest you attach your sample data as .csv and also provide the code which reads this .csv and creates the structure of the source data set you're dealing with.

Occasional Contributor
Posts: 5

Re: How to concert the following dataset to stacked format?

Hi Patrick,

Thank you a lot for your reply. I attached a sample file.

Some friend suggested:

  1. data abc_1;
  2. set abc (rename=(code=date_c));
  3. array num{*} _numeric_;
  4. do i = 1 to dim(num) by 3;
  5. code = scan(vlabel(num), 1, '()');
  6. p = num;
  7. p2 = num[i+1];
  8. p3 = num[i+2];
  9. output;
  10. end;
  11. keep date_c code p p2 p3;
  12. run;

But that does not work. Would you suggest what adjustment I should make?

Best regards!

Super User
Super User
Posts: 6,499

Re: How to concert the following dataset to stacked format?

Write it out to multiple datasets and then merge them back together.

data names(keep=col name)

     codes(keep=col code)

     values(keep=col date value)

;

infile cards dsd dlm='|' truncover ;

retain ncols ;

length col 8 name $100 code $30 date value 8;

format date yymmdd10. ;

informat date ddmmyy10. ;

length dummy $10;

input dummy @ ;

select (dummy);

  when ('Name') do;

    do col=1 by 1 until (name=' ') ;

      input name @;

      if name ne ' ' then output names ;

    end;

    ncols=col-1;

  end;

  when ('Code') do;

    do col=1 to ncols ;

      input code @ ;

      output codes ;

    end;

  end;

  other do ;

    input @1 date @;

    do col=1 to ncols;

      input value @ ;

      output values;

    end;

  end;

end;

cards;

Name|CHINA NTH.ET.PTL.HDG. DEAD - DELIST.05/04/13|CHINA NORTH EAST - TOTAL ASSETS|CHINA NORTH EAST - CASH DIVIDENDS PAID - TOTAL|XYRATEX

Code|26492K(P)|26492K(WC02999)|26492K(WC04551)|29060T(P)

15-04-2009|1.52|110192|0|2.27

16-04-2009|1.5|110192|0|2.26

17-04-2009|1.5|110192|0|2.4

run;

proc sql noprint ;

create table all as

   select b.code,c.date,c.value,a.name

   from names a

      , codes b

      , values c

   where a.col = b.col

     and b.col = c.col

     and a.col = c.col

   order by 1,2

;

quit;

Ask a Question
Discussion stats
  • 4 replies
  • 233 views
  • 0 likes
  • 3 in conversation