so with out writing all columns names how can I insert a column only by naming the column to the right and left?
I like the elegance of the solution @ChrisNZ has offered. But since you said you want to specify nothing but the names of the surrounding variables, I'd offer one where you need to name only the variable after which you want the insertion (and, of course, necessarily the name and other attributes of the variable you want to insert). Thus, if you originally have a data set:
data have ;
retain A "A" B "BB" C "CCC" E "EEEEE" F "FFFFFF" G 1 ;
do G = 1 to 10 ;
output ;
end ;
run ;
and want to insert a variable D after variable C, you could code as follows:
%let v_aft = C ;
%let v_ins = D ;
%let v_len = $4 ; * if you want D as numeric, just code 8 instead of $4 ;
data _null_ ;
do until (z) ;
set sashelp.vcolumn end = z ;
where libname = "WORK" and memname = "HAVE" ;
length v_list $ 32767 ;
v_list = catx (" ", v_list, name) ;
if name = "&v_aft" then v_list = catx (" ", v_list, "&v_ins") ;
end ;
call symputx ("v_list", v_list) ;
run ;
data want ;
retain &v_list ;
length &v_ins &v_len ;
call missing (&v_ins) ;
set have ;
run ;
Now if you look at the data set WANT, you'll see the variable D right between C and E. The piece producing V_LIST may look rather maladroit, yet in reality it exacts little overhead since it only reads the metadata.
Paul D.
Consider you have the data as below , this is a untested code but hopefully should work
data have;
input alphabets $;
cards;
A
B
C
E
F
G
H
;
create a dummy dataset;
data dum;
input alphabets $;
cards;
A
B
C
D
E
F
G
H
;
data all;
merge have dum;
by alphabets ;
run;
proc transpose data=all;
var alphabets;
run;
thank you for you help but the issue is there are two many columns to list out all column names.
Like this?
data WANT;
if 0 then set HAVE(keep=A--C);
length D 8;
if 0 then set HAVE(keep=E--G);
more code.. set HAVE; more code...
run;
I like the elegance of the solution @ChrisNZ has offered. But since you said you want to specify nothing but the names of the surrounding variables, I'd offer one where you need to name only the variable after which you want the insertion (and, of course, necessarily the name and other attributes of the variable you want to insert). Thus, if you originally have a data set:
data have ;
retain A "A" B "BB" C "CCC" E "EEEEE" F "FFFFFF" G 1 ;
do G = 1 to 10 ;
output ;
end ;
run ;
and want to insert a variable D after variable C, you could code as follows:
%let v_aft = C ;
%let v_ins = D ;
%let v_len = $4 ; * if you want D as numeric, just code 8 instead of $4 ;
data _null_ ;
do until (z) ;
set sashelp.vcolumn end = z ;
where libname = "WORK" and memname = "HAVE" ;
length v_list $ 32767 ;
v_list = catx (" ", v_list, name) ;
if name = "&v_aft" then v_list = catx (" ", v_list, "&v_ins") ;
end ;
call symputx ("v_list", v_list) ;
run ;
data want ;
retain &v_list ;
length &v_ins &v_len ;
call missing (&v_ins) ;
set have ;
run ;
Now if you look at the data set WANT, you'll see the variable D right between C and E. The piece producing V_LIST may look rather maladroit, yet in reality it exacts little overhead since it only reads the metadata.
Paul D.
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.