topic Re: How to concert the following dataset to stacked format? in SAS Programming

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

99_99_ — Sat, 12 Oct 2013 09:45:36 GMT

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

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

99_99_ — Sat, 12 Oct 2013 09:46:47 GMT

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

Thank you a lot!

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

Patrick — Sat, 12 Oct 2013 10:09:51 GMT

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.

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

Tom — Sat, 12 Oct 2013 13:34:42 GMT

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;

other do ;

input @1 date @;

do col=1 to ncols;

input value @ ;

output values;

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;

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

99_99_ — Mon, 14 Oct 2013 09:43:52 GMT

Hi Patrick,

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

Some friend suggested:

data abc_1;
set abc (rename=(code=date_c));
array num{*} _numeric_;
do i = 1 to dim(num) by 3;
code = scan(vlabel(num), 1, '()');
p = num;
p2 = num[i+1];
p3 = num[i+2];
output;
end;
keep date_c code p p2 p3;
run;

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

Best regards!