report - 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!?

question from:https://stackoverflow.com/questions/65925633/sas-add-new-multiple-columns-based-on-other-columns-value-using-loop

Answer 1

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