Hi
I am trying to concatenate data which have the form
RegAdjusted | mean_objectprice_month2 | regperiod2 | mean_objectprice_month3 | regperiod3 | mean_objectprice_month4 | regperiod4 | mean_objectprice_month5 | regperiod5 | mean_objectprice_month6 | regperiod6 | mean_objectprice_month7 | regperiod7 |
AA49918 | 187000 | 201010 | 80000 | 201501 | ||||||||
AA50059 | 135000 | 201703 | 120000 | 201712 | ||||||||
AA50067 | 106250 | 201308 | ||||||||||
AA50074 | 152600 | 201309 | 149000 | 201407 | 145000 | 201408 | ||||||
AA50085 | 150000 | 201106 | ||||||||||
AA50114 | 270000 | 201101 | ||||||||||
AA50221 | 387000 | 201101 | ||||||||||
AA50235 | 270000 | 201211 | ||||||||||
AA50237 | 96500 | 201601 | ||||||||||
AA50262 | 64535 | 201406 | 75000 | 201602 | ||||||||
AA50291 | 39000 | 201703 | ||||||||||
AA50457 | 24035 | 201408 | ||||||||||
AA50497 | 158000 | 201002 | 158000 | 201002 | ||||||||
AA50506 | 79000 | 201301 | ||||||||||
AA50644 | 49977 | 201410 | ||||||||||
AA50689 | 299980 | 201301 | ||||||||||
AA50768 | 160000 | 201703 | ||||||||||
AA50828 | 69977 | 201606 | ||||||||||
AA50830 | 150500 | 201508 | ||||||||||
AA50840 | 118000 | 201402 | 100000 | 201801 | ||||||||
AA50856 | 91873,5 | 201108 | ||||||||||
AA50949 | 125000 | 201307 | ||||||||||
AA51050 | 95000 | 201612 | ||||||||||
AA51061 | 144950 | 201107 | ||||||||||
AA51137 | 65000 | 201207 | ||||||||||
AA51151 | 189000 | 201709 | ||||||||||
AA51167 | 89000 | 201310 | ||||||||||
AA51194 | 326438 | 201402 | 300000 | 201404 | 190000 | 201409 | 214500 | 201411 | 299900 | 201501 | ||
AA51223 | 45000 | 201106 | 45000 | 201106 | ||||||||
AA51272 | 179500 | 201509 | ||||||||||
AA51285 | 35000 | 201607 | ||||||||||
AA51296 | 131000 | 201405 | ||||||||||
AA51358 | 80535 | 201503 | ||||||||||
AA51388 | 285000 | 201512 | 280000 | 201605 | ||||||||
AA51392 | 140000 | 201211 | ||||||||||
AA51404 | 310000 | 201701 | ||||||||||
AA51447 | 273000 | 201510 | ||||||||||
AA51466 | 84977 | 201408 | 67000 | 201710 | ||||||||
AA51479 | 40000 | 201711 | ||||||||||
AA51563 | 208000 | 201201 | 129500 | 201605 | 129500 | 201605 | 125000 | 201608 |
To look something like this
RegAdjusted | Price1 | Period1 | Price2 | Period2 | Price3 | Period3 | Price4 | Period4 | Price5 | Period5 | Price6 | Period6 |
AA49918 | 187000 | 201010 | 80000 | 201501 | ||||||||
AA50059 | 135000 | 201703 | ||||||||||
AA50067 | 106250 | 201308 | ||||||||||
AA50074 | 152600 | 201309 | 149000 | 201407 | 145000 | 201408 | ||||||
AA50085 | 150000 | 201106 | ||||||||||
AA50114 | 270000 | 201101 | ||||||||||
AA50221 | 387000 | 201101 | ||||||||||
AA50235 | 270000 | 201211 | ||||||||||
AA50237 | 96500 | 201601 | ||||||||||
AA50262 | 64535 | 201406 | 75000 | 201602 | ||||||||
AA50291 | 39000 | 201703 | ||||||||||
AA50457 | 24035 | 201408 | ||||||||||
AA50497 | 158000 | 201002 | 158000 | 201002 | ||||||||
AA50506 | 79000 | 201301 | ||||||||||
AA50644 | 49977 | 201410 | ||||||||||
AA50689 | 299980 | 201301 | ||||||||||
AA50768 | 160000 | 201703 | ||||||||||
AA50828 | 69977 | 201606 | ||||||||||
AA50830 | 150500 | 201508 | ||||||||||
AA50840 | 118000 | 201402 | ||||||||||
AA50856 | 91873,5 | 201108 | ||||||||||
AA50949 | 125000 | 201307 | ||||||||||
AA51050 | 95000 | 201612 | ||||||||||
AA51061 | 144950 | 201107 | ||||||||||
AA51137 | 65000 | 201207 | ||||||||||
AA51151 | 189000 | 201709 | ||||||||||
AA51167 | 89000 | 201310 | ||||||||||
AA51194 | 326438 | 201402 | 300000 | 201404 | 190000 | 201409 | 214500 | 201411 | 299900 | 201501 | ||
AA51223 | 45000 | 201106 | 45000 | 201106 | ||||||||
AA51272 | 179500 | 201509 | ||||||||||
AA51285 | 35000 | 201607 | ||||||||||
AA51296 | 131000 | 201405 | ||||||||||
AA51358 | 80535 | 201503 | ||||||||||
AA51388 | 285000 | 201512 | 280000 | 201605 | ||||||||
AA51392 | 140000 | 201211 | ||||||||||
AA51404 | 310000 | 201701 | ||||||||||
AA51447 | 273000 | 201510 | ||||||||||
AA51466 | 84977 | 201408 | 67000 | 201710 | ||||||||
AA51479 | 40000 | 201711 | ||||||||||
AA51563 | 208000 | 201201 | 129500 | 201605 | 129500 | 201605 | 125000 | 201608 |
Trying with call catx and then split, but I can't get it to work
Untested:
data want (keep=regadjusted period1-period6 price1-price6);
set have;
array pricesold {*} mean_objectprice_month2-mean_objectprice_month7;
array periodsold {*} $ regperiod2-regperiod7;
array pricesnew {*} price1-price6;
array periodsnew {*} $ period1-period6;
i1 = 1;
do i = 1 to dim(procesold);
if pricesold{i} ne .
then do;
pricesnew{i1} = pricesold{i};
periodsnew{i1} = periodsold{i};
i1 + 1;
end;
end;
run;
For tested code, supply example data in a data step with datalines.
Untested:
data want (keep=regadjusted period1-period6 price1-price6);
set have;
array pricesold {*} mean_objectprice_month2-mean_objectprice_month7;
array periodsold {*} $ regperiod2-regperiod7;
array pricesnew {*} price1-price6;
array periodsnew {*} $ period1-period6;
i1 = 1;
do i = 1 to dim(procesold);
if pricesold{i} ne .
then do;
pricesnew{i1} = pricesold{i};
periodsnew{i1} = periodsold{i};
i1 + 1;
end;
end;
run;
For tested code, supply example data in a data step with datalines.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.