Name | CHINA NTH.ET.PTL.HDG. DEAD - DELIST.05/04/13 | CHINA NORTH EAST - TOTAL ASSETS | CHINA NORTH EAST - CASH DIVIDENDS PAID - TOTAL | XYRATEX | XYRATEX LTD. - TOTAL ASSETS | XYRATEX LTD. - CASH DIVIDENDS PAID - TOTAL | CNOOC SPN.ADR 1:100 | CNOOC LTD - TOTAL ASSETS | CNOOC LTD - CASH DIVIDENDS PAID - TOTAL | CHINAWE.COM | CHINAWE.COM - TOTAL ASSETS | CHINAWE.COM - CASH DIVIDENDS PAID - TOTAL |
Code | 26492K(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-2009 | 1.52 | 110192 | 0 | 2.27 | 347981 | 0 | 116.81 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
16-04-2009 | 1.5 | 110192 | 0 | 2.26 | 347981 | 0 | 114.42 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
17-04-2009 | 1.5 | 110192 | 0 | 2.4 | 347981 | 0 | 119 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
20-04-2009 | 1.5 | 110192 | 0 | 2.46 | 347981 | 0 | 112.65 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
21-04-2009 | 1.48 | 110192 | 0 | 2.47 | 347981 | 0 | 113.07 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
22-04-2009 | 1.47 | 110192 | 0 | 2.93 | 347981 | 0 | 108.12 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
23-04-2009 | 1.52 | 110192 | 0 | 3.05 | 347981 | 0 | 112.15 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
24-04-2009 | 1.54 | 110192 | 0 | 3.34 | 347981 | 0 | 115.55 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
27-04-2009 | 1.71 | 110192 | 0 | 3.3 | 347981 | 0 | 110.32 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
28-04-2009 | 1.8 | 110192 | 0 | 3.3 | 347981 | 0 | 108.95 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
29-04-2009 | 1.9 | 110192 | 0 | 3.39 | 347981 | 0 | 112.75 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
30-04-2009 | 2.08 | 110192 | 0 | 3.35 | 347981 | 0 | 111.35 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
01-05-2009 | 2.15 | 110192 | 0 | 3.54 | 347981 | 0 | 114.55 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
04-05-2009 | 2.55 | 110192 | 0 | 3.53 | 347981 | 0 | 126.56 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
05-05-2009 | 2.49 | 110192 | 0 | 3.55 | 347981 | 0 | 124 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
06-05-2009 | 2.79 | 110192 | 0 | 3.53 | 347981 | 0 | 127.46 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
07-05-2009 | 2.95 | 110192 | 0 | 3.36 | 347981 | 0 | 122.74 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
08-05-2009 | 2.99 | 110192 | 0 | 3.46 | 347981 | 0 | 130.4 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
11-05-2009 | 3 | 110192 | 0 | 3.35 | 347981 | 0 | 124.97 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
12-05-2009 | 2.99 | 110192 | 0 | 3.38 | 347981 | 0 | 124.96 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
13-05-2009 | 2.85 | 110192 | 0 | 3.3 | 347981 | 0 | 127.31 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
14-05-2009 | 2.7 | 110192 | 0 | 3.34 | 347981 | 0 | 125.07 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
15-05-2009 | 2.87 | 110192 | 0 | 3.37 | 347981 | 0 | 123.77 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
18-05-2009 | 2.93 | 110192 | 0 | 3.46 | 347981 | 0 | 129.86 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
19-05-2009 | 2.93 | 110192 | 0 | 3.56 | 347981 | 0 | 133.22 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
20-05-2009 | 2.9 | 110192 | 0 | 3.46 | 347981 | 0 | 132.47 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
21-05-2009 | 2.86 | 110192 | 0 | 3.42 | 347981 | 0 | 128.91 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
22-05-2009 | 2.99 | 110192 | 0 | 3.45 | 347981 | 0 | 126.78 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
25-05-2009 | 2.99 | 110192 | 0 | 3.45 | 347981 | 0 | 126.78 | 35487512 | 2075279 | 0.01 | 2152 | 0 |
26-05-2009 | 2.97 | 110192 | 0 | 3.6 | 347981 | 0 | 123.56 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
27-05-2009 | 2.94 | 110192 | 0 | 3.45 | 347981 | 0 | 124.81 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
28-05-2009 | 3.57 | 110192 | 0 | 3.33 | 347981 | 0 | 126.47 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
29-05-2009 | 3.89 | 110192 | 0 | 3.3 | 347981 | 0 | 134.02 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
01-06-2009 | 4.62 | 110192 | 0 | 3.57 | 347981 | 0 | 143.93 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
02-06-2009 | 4.57 | 110192 | 0 | 3.5 | 347981 | 0 | 141.86 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
03-06-2009 | 4.23 | 110192 | 0 | 3.6 | 347981 | 0 | 136.47 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
04-06-2009 | 4.36 | 110192 | 0 | 3.76 | 347981 | 0 | 141.07 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
05-06-2009 | 4.3 | 110192 | 0 | 3.92 | 347981 | 0 | 142.43 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
08-06-2009 | 4.35 | 110192 | 0 | 4.11 | 347981 | 0 | 139.86 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
09-06-2009 | 4.36 | 110192 | 0 | 4.39 | 347981 | 0 | 138 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
10-06-2009 | 4.64 | 110192 | 0 | 4.4 | 347981 | 0 | 139.77 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
11-06-2009 | 5.09 | 110192 | 0 | 4.43 | 347981 | 0 | 141.71 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
12-06-2009 | 5.29 | 110192 | 0 | 4.37 | 347981 | 0 | 139.76 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
15-06-2009 | 5.82 | 110192 | 0 | 4.31 | 347981 | 0 | 133.3 | 35487512 | 2075279 | 0.005 | 2152 | 0 |
16-06-2009 | 5.28 | 110192 | 0 | 4.04 | 347981 | 0 | 126.08 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
17-06-2009 | 4.37 | 110192 | 0 | 3.83 | 347981 | 0 | 123.93 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
18-06-2009 | 3.95 | 110192 | 0 | 3.8 | 347981 | 0 | 124.76 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
19-06-2009 | 4.52 | 110192 | 0 | 3.85 | 347981 | 0 | 124.01 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
22-06-2009 | 3.9 | 110192 | 0 | 3.7 | 347981 | 0 | 119.46 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
23-06-2009 | 3.74 | 110192 | 0 | 3.73 | 347981 | 0 | 119.38 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
24-06-2009 | 4.25 | 110192 | 0 | 3.95 | 347981 | 0 | 122.82 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
25-06-2009 | 4.62 | 110192 | 0 | 4.6 | 347981 | 0 | 126.18 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
26-06-2009 | 4.59 | 110192 | 0 | 4.96 | 347981 | 0 | 126.25 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
29-06-2009 | 4.54 | 110192 | 0 | 4.98 | 347981 | 0 | 126.16 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
30-06-2009 | 4.44 | 110192 | 0 | 5 | 347981 | 0 | 123.03 | 35487512 | 2075279 | 0.004 | 2152 | 0 |
In real data, there are much more companies than listed above and the time period is much longer.
Thank you a lot!
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.
Hi Patrick,
Thank you a lot for your reply. I attached a sample file.
Some friend suggested:
But that does not work. Would you suggest what adjustment I should make?
Best regards!
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.