Help using Base SAS procedures

Data replacing

Accepted Solution Solved
Reply
Contributor
Posts: 53
Accepted Solution

Data replacing

 

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


Accepted Solutions
Solution
‎03-01-2018 09:54 AM
Super User
Posts: 10,217

Re: Data replacing

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

View solution in original post


All Replies
Solution
‎03-01-2018 09:54 AM
Super User
Posts: 10,217

Re: Data replacing

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 146 views
  • 0 likes
  • 2 in conversation