Hello,
I have a database where 12,000 variables are named "A0122_40", "A0122_45", "A0122_50" and so on.
I would like to rename them by keeping in the initial name the numbers from 2 to 5.
I would then like to create variables adding all the columns with the same name.
I don't know if better to do two steps (rename then create new variable with SUM) or do directly a procedure to create the new variables.
I already try the rename step from several ways but it still stop because of errors.
Last try was :
proc contents data=mine.petitTEST1 out = mine.petitcontents (keep=name where=(name not in ("DEPCOM","NAIS","SEXE"))) noprint; run; data mine.petitTEST2;
set mine.petitcontents ;
new_name=cats(name,'=','var',substr(name,2,4));
output; run;
proc sql;
select distinct new_name into : renlist separated by ' ' from mine.petitTEST2;
quit;
proc datasets lib= mine nolist;
modify petitTEST1 ;
rename 'A0:' = &renlist. ; run;
quit;
Message of error was :
ERROR 22-322: Expecting un nom.
It seems to me you need to keep more of _name_, as there are a lot of ambiguities. And the non-missing values of the second picture can't show up in your first picture, as you are a long way away from the years where they will show up.
12,000 variables in a single table? Who comes up with such nonsense?
My trust in the further survival of humankind just took a big hit.
This really cries out for transposing as a first step. Then it's a simple transformation of a single column.
Do you have a pattern for all those variable names? eg everything starts with "A0"?
yes they are
This is a small sample
Try this:
(adapt the libname)
proc transpose
data=sascomm.joel2
out=trans (
rename=(col1=value)
where=(value ne .)
)
;
by idnew; /* put all non-"A0" variables here */
var A0:;
run;
data sascomm.want;
length name $4;
set trans;
name = substr(_name_,2,4);
drop _name_;
run;
Then do
proc sort data=have;
by .......; /* put all non-"A0" variables here */
run;
proc transpose data=have out=trans;
by .......; /* put all non-"A0" variables here */
var A0:;
run;
data want;
set trans;
_name_ = substr(_name_,2,4);
run;
If you have lots of missing values, you can remove those observations with a proper where= dataset option during the transpose. You might save a lot of disk space.
Thank you, that's a big step forward for me.
How can I keep the occurrence information of each renamed variable (see pictures)?
It seems to me you need to keep more of _name_, as there are a lot of ambiguities. And the non-missing values of the second picture can't show up in your first picture, as you are a long way away from the years where they will show up.
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.