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
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.
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.
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;
See SAS Variable Lists for ways to list variables:
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;
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.
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.