rename all variable then compute

[annaba]

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.

[Kurt_Bremser]

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.

[annaba]

I do with that I can with what administration gave me.
Could you imagine there is less and help me?

[Kurt_Bremser]

Do you have a pattern for all those variable names? eg everything starts with "A0"?

[annaba]

yes they are

[annaba]

This is a small sample

[Kurt_Bremser]

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;

[Kurt_Bremser]

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.

[annaba]

Thank you, that's a big step forward for me.
How can I keep the occurrence information of each renamed variable (see pictures)?

[Kurt_Bremser · Accepted solution]

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.

[annaba]

Thank you so much. Great help.