topic Re: Creating multiple columns using single column in SAS Programming

Creating multiple columns using single column

india2016 — Wed, 13 Apr 2022 13:14:28 GMT

Hello Team,

I am trying to create multiple columns using single column as showin in below picture.

First row has column name (tre,iop,df) from RL_DATA column.

RU_ID	RL_DATA
1	TRE,IOP,DF
1	0,2,3
1	9,8,0
1	8,8,9

I want table in below format

RU_ID	TRE	IOP	DF
1	0	2	3
1	9	8	0
1	8	8	9

Does anyone have shortcut method or similar scenario faced earlier then can you please give me brief idea so that I can code in that way.

What I am tried till now.

i) I have create macro of RL_Data first row (TRE,IOP,DF) using into :

ii) Creating loop to pass each time column name and create new column in same table.

But facing a lot of challenges in section ii).

Please let me know the path to solve this scenario so that I can code accordingly.

Re: Creating multiple columns using single column

Tom — Wed, 13 Apr 2022 13:25:31 GMT

Why not just write it out as a CSV file and then use PROC IMPORT to read it back in?

filename csv temp;
data _null_;
  set HAVE ;
  file csv ;
  if _n_=1 then put 'RU_ID' @; 
  else put RU_ID @;
  put ',' rl_data;
run;

proc import datafile=csv dbms=csv out=want replace;
run;

Re: Creating multiple columns using single column

Tom — Wed, 13 Apr 2022 13:31:50 GMT

No need for macro.

data tall ;
  row+1;
  set have ;
  length name $32 value 8;
  if _n_=1 then do;
     namelist = rl_data;
     delete;
     retain namelist;
  end;
  do index=1 to countw(rl_data,',');
      name=coalescec(scan(namelist,index,','),cats('var',index));
       value=input(scan(rl_data,index,','),32.);
     output;
  end;
  keep row ru_id name value;
run;

proc transpose data=tall out=want(drop=_name_);
  by row ru_id;
  id name;
  var value;
run;

Re: Creating multiple columns using single column

ballardw — Wed, 13 Apr 2022 14:05:52 GMT

Does RL_data have a different value of the first record that you use for naming variables for a different value of RU_ID?

Does every record have exactly 3 values for RL_data?

Are the new variables supposed to be numeric or character?

Re: Creating multiple columns using single column

Kurt_Bremser — Wed, 13 Apr 2022 15:08:20 GMT

This is what you tried to do:

data have;
infile datalines dlm="09"x;
input RU_ID $ RL_DATA :$10.;
datalines;
1	TRE,IOP,DF
1	0,2,3
1	9,8,0
1	8,8,9
;

data _null_;
set have (obs=1);
call symputx('varlist',translate(rl_data," ",","));
run;

data want;
set have (firstobs=2);
array new {*} $ &varlist.;
do i = 1 to dim(new);
  new{i} = scan(rl_data,i,",");
end;
drop i rl_data;
run;

You need to add code if you want to convert the values to numeric.

Re: Creating multiple columns using single column

india2016 — Thu, 14 Apr 2022 05:57:26 GMT

Thanks Kurt.

I worked very well.

Thank you so much for your prompt help.

Re: Creating multiple columns using single column

india2016 — Thu, 14 Apr 2022 06:03:06 GMT

Does every record have exactly 3 values for RL_data?

--Yes.

Are the new variables supposed to be numeric or character?

--Numeric would be preferable.

And actually Kurt_Bremser has resolved the basic issue which I was facing . Now I can build the code on the basis of that.

@ballardw

Thank you so much for putting your efforts and time.

Re: Creating multiple columns using single column

india2016 — Thu, 14 Apr 2022 06:05:41 GMT

Thanks Tom for your suggestion and code.

I can do it this way as well but I have created scenarion the data is too large. so

Re: Creating multiple columns using single column

india2016 — Thu, 14 Apr 2022 06:06:59 GMT

Thanks Tom for your time and quick help. this code is also solve my requirement,