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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.