Data replacing

[Kiteulf]

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

[Kurt_Bremser · Accepted solution]

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.