topic Re: Proc Transpose in SAS Programming

Proc Transpose

Ronein — Sun, 13 Sep 2020 06:35:28 GMT

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;

Re: Proc Transpose

Ronein — Sun, 13 Sep 2020 06:43:34 GMT

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;

Re: Proc Transpose

Kurt_Bremser — Sun, 13 Sep 2020 07:24:39 GMT

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.

Re: Proc Transpose

Ronein — Sun, 13 Sep 2020 10:23:02 GMT

May you please give more examples when using

options validvarname=v7;

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

Re: Proc Transpose

Kurt_Bremser — Sun, 13 Sep 2020 12:11:13 GMT

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.

Re: Proc Transpose

Ronein — Mon, 14 Sep 2020 05:06:37 GMT

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

Re: Proc Transpose

Kurt_Bremser — Mon, 14 Sep 2020 06:10:30 GMT

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