SAS Add new multiple columns based on other column's value using loop

Question

I have this kind of data table (it's just a part of large dataset) that I created using PROC MEANS and PROC SQL in SAS studio. I want to add several new columns in here which shows pct_missing of each year. Because, right now, we have same variables in multiple rows bcz i grouped it by year. Is there any way I can add these kind of columns using loop?

Current data(original data table):

what I expect to have: (wanna add all new columns pct_missing_2004 ~ pct_missing_2018 showing pct_missing of variable in each year) (the value of pct_missing_2004, 2005 written below is just an example!!!)

Is there anyway we can do this using loop or any other good way in SAS!?

Why would you still have a year column then? Please provide a more complete example where the numbers align to illustrate your actual problem. I can't tell if you just need a transpose or to redesign your whole process. The answer also likely depends on if you're dealing with a single data set or multiple data sets. — Reeza
@Reeza just realized I made mistake uploading the wrong pic for second one! yes what i expected was actually without year column and with only one distinct variable for each. :) Luckily, I just found a way (Transpose!) to do what i wanted. thank u for the comments! — Vie de Marion

mAndroid mAndroid · Accepted Answer · 2021-01-27T20:39:30

I'm not 100% I'm sure what you want here but perhaps a transpose may help.

Check out this example below.

data have; 
 input 
    year 4. 
    variable $1.
    pct_missing 3.;
 CARDS;
2011A100
2011B90
2010A80
2010B70
2010C60
 ;
 run; 
 
 proc sort 
  data=have;
  by variable; 
 run; 
 
 
 Proc Transpose
    data=have
    out=want (drop=_name_)
    prefix=pct_missing;
    id year;
    var  pct_missing;
    by variable ; 
 run;

mAndroid

SAS Add new multiple columns based on other column's value using loop

1 Answers