topic Re: Array to loop column name variable wide to long in SAS Programming

Array to loop column name variable wide to long

jayakumarmm — Fri, 09 Sep 2016 20:42:16 GMT

I am finding difficulty in assigning column name variables to array list. I am trying to create a new dataset with column lable name in a field(NEW_COLUMN1) and column value(NEW_COLUMN2) in another field. Given below is the code which I have tried

data work.dataset_new;
set work.dataset_created;
LENGTH NEW_COLUMN1 $ 100;
FORMAT NEW_COLUMN2 6.;
Array char {*} _character_;
Array num {*} _numeric_;
do i = 1 to dim(char); 
NEW_COLUMN1 = vlabel(char{i});
NEW_COLUMN2 = (char{i});
end;
do i = 1 to dim(num); 
NEW_COLUMN1 = vlabel(char{i});
NEW_COLUMN2 = (char{i});
end;
run;

Actual_dataset:

COLUMN1 COLUMN2 COLUMN3 COLUMN4 COLUMN5 COLUMN6 COLUMN7 COLUMN8 COLUMN9

101 102 103 104 105 106 3 22 55

Expected dataset

NEW_COLUMN1 NEW_COLUMN2
COLUMN6_label 106
COLUMN7_label 3
COLUMN8_label 22

Re: Array to loop column name variable wide to long

Tom — Fri, 09 Sep 2016 21:04:15 GMT

Sounds like a simple transpose.

Let's assume you have this dataset. We can call it HAVE.;

data have;
  input COLUMN1-COLUMN9;
  label column1='This is what a label looks like';
cards;
101 102 103 104 105 106 3 22 55
;

Now you want to create a tall skinny table with name/label/value pairs instead.

data want ;
  set have;
  array all _numeric_;
  length name $32 label $256 value 8;
  do i=1 to dim(all);
    name=vname(all(i));
    label=vlabel(all(i));
    value = all(i);
    output;
  end;
  keep name label value;
run;

Here is the result.

Re: Array to loop column name variable wide to long

jayakumarmm — Fri, 09 Sep 2016 21:57:07 GMT

Thanks a lot for your soluton it saved me lot of typing. I have a clarificaton in selecting the variables based on variable name postion and ignoring few variables based on variable names.

For example: Ignore variable name looks like below code and ike to know another logic i.e. how to consider variable names in the array between two variables names COLUMN5 - COLUMN9 and drop COLUMN5 & COLUMN9 from dataset

data want ;
  set have;
  array all _numeric_;
  length name $32 label $256 value 8;
  do i=1 to dim(all);
    IF vname(all(i))  not like 'Sc%' then
do;
    name=vname(all(i));
    label=vlabel(all(i));
    value = all(i);
    output;
  end;
end;
  keep name label value;
run;

Re: Array to loop column name variable wide to long

Reeza — Fri, 09 Sep 2016 22:06:59 GMT

See SAS Variable Lists for ways to list variables:

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#p0wphcpsfgx6o7n1sjtqzizp1n39.htm

Re: Array to loop column name variable wide to long

ballardw — Fri, 09 Sep 2016 23:09:04 GMT

If there are not a lot variables you may be better off to explicitly list then instead of using like.

Instead of

IF vname(all(i)) not like 'Sc%' then do;

IF upcase(vname(all(i))) not in ('COLUMN5' 'COLUMN9') then do;

Re: Array to loop column name variable wide to long

jayakumarmm — Sat, 10 Sep 2016 22:55:41 GMT

I am struggling for 4 to 5 hrs to build a dataset in SAS , I have attached the current and expected dataset. Now I am need of expertise advise.

Re: Array to loop column name variable wide to long

Tom — Sun, 11 Sep 2016 00:02:15 GMT

What are you having trouble with? Looks simple to me.

data have ;
  length MONITER_ID $8 DATE_REC 8
         A_STATUS $8 A_ID 8 B_STATUS $8 B_ID 8 C_STATUS $8 C_ID 8
         ARC_NAM $8 ARC_DAT 8
  ;
  informat date_rec arc_dat mmddyy10.;
  format date_rec arc_dat yymmdd10.;
  input moniter_id -- arc_dat ;
cards;
MONI_ID1 2/3/2016 YES  1 NO  2 YES  5 ADAM 3/9/2016
MONI_ID2 3/3/2016 NO  2 YES  3 YES  6 KAN 8/9/2016
MONI_ID3 4/3/2016 NO  3 Complete 1 complete 7 KAN 9/9/2016
MONI_ID4 6/3/2016 YES  4 NO  2 YEs  8 ADAM 10/9/2016
;
data want ;
 set have ;
 array stat A_STATUS B_STATUS C_STATUS ;
 array id   A_ID B_ID C_ID ;
 do i=1 to dim(stat);
   length status $32 ;
   status = vname(stat(i));
   value = stat(i);
   id_code = id(i);
   output;
 end;
 keep moniter_id status value id_code ;
run;

proc print; run;