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.
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.