BookmarkSubscribeRSS Feed
99_99_
Calcite | Level 5
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
4 REPLIES 4
99_99_
Calcite | Level 5

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

Thank you a lot!

Patrick
Opal | Level 21

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.

99_99_
Calcite | Level 5

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!

Tom
Super User Tom
Super User

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;

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 4 replies
  • 489 views
  • 0 likes
  • 3 in conversation