Proc Transpose

[Ronein]

Hello

What is the way that the names of the new columns will be 

Y30_12_19   Y30_12_18  Y28_08_17   Y26_09_19   Y25_06_18

instead of:

Y30-12-19   Y30-12-18  Y28-08-17   Y26-09-19   Y25-06-18

Data t;
input cid  date ddmmyy8. flag $;
format date ddmmyy8.;
cards;
1 30-12-19 Y
1 30-12-18 N
1 28-08-17 N
2 26-09-19 Y
2 25-06-18 N
;
Run;

proc transpose data=t out=want(drop=_name_) prefix=Y;
BY CID notsorted;
ID date;
var flag;
Run;
 

[Ronein]

I found solution.

I will be glad to see more solutions

data t2 ;
set t;
date_char=put(date,ddmmyy8.);
DD=scan(date_char,1,"/");
MM=scan(date_char,2,"/");
YYYY=CATS("20",scan(date_char,3,"/"));
date_char_new=catx("_",DD,MM,YYYY);
run;

proc transpose data=t2 out=want (drop=_name_) prefix=Y;
by cid;
id date_char_new;
var flag;
Run;

[Kurt_Bremser · Accepted solution]

Set

options validvarname=v7;

before running the TRANSPOSE, and it will take care of invalid characters on its own.

BTW: still using 2-digit years after the Y2K scare is, to be polite, extremely stupid.

Example:

30_12_19

could be December 30, 1919, or December 30, 2019, or (use any other year after 1600 that ends in 19), or December 19, 1930, or December 19, 2030, or (see above);

Using th standard ISO format E8601DA10. or YYMMDD10. will fix that.

[Ronein]

May you please give more examples when using 

options validvarname=v7;

Is it used most in proc transpose or also in summary procs?

[Kurt_Bremser]

Make use of the documentation: VALIDVARNAME System Option 

 What comes into play here is a side-effect of the option: before it was introduced, TRANSPOSE had to try its best to make valid SAS names (max 32 characters, can only contain letters, digits and underlines, must start with a letter or underline) out of the contents of the ID variable(s). That's what I forced by setting the option to V7.

With the usual setting nowadays (ANY), TRANSPOSE can mostly keep what's in the ID variables, so it will rarely make any changes.

A similar effect exists for PROC IMPORT, where a setting of V7 will also force a conversion to valid SAS names.

[Ronein]

What is the way to close(terminate) the option "options validvarname=v7;" ?

[Kurt_Bremser]

You don't "terminate" it, you set it to values according to your needs. These are all described in the documentation I linked to.