topic Re: proc transpose in SAS Procedures

proc transpose

sasphd — Tue, 08 Feb 2022 17:35:55 GMT

hello

I try to run proc transpose but it does not work. there is no erro in log but the data generated is empty.

my data looks like. the problem is that the date format is $12. as I import it as text from excel


/****** TRANSPOSER LA TABLE  **********/
proc transpose data=Global_MS out=style_box    ;
by SecId;
run;

have

secid 20020201 20020301 20020401

1 Large small large

2 small small large

want

secid date style

1 20020201 Large

1 20020301 small

1 20020401 large

2 20020201 small

2 20020301 small

2 20020401 large

Re: proc transpose

Reeza — Tue, 08 Feb 2022 17:47:05 GMT

proc transpose data=Global_MS out=style_box prefix=D_    ;
by SecId;
ID date;
value style;
run;

How does that work for you?

@sasphd wrote:

hello

I try to run proc transpose but it does not work. there is no erro in log but the data generated is empty.

my data looks like. the problem is that the date format is $12. as I import it as text from excel
/****** TRANSPOSER LA TABLE  **********/
proc transpose data=Global_MS out=style_box    ;
by SecId;
run;
have

secid 20020201 20020301 20020401

1 Large small large

2 small small large

want

secid date style

1 20020201  Large

1 20020301   small

1 20020401 large

2 20020201 small

2 20020301   small

2 20020401 large

Re: proc transpose

sasphd — Tue, 08 Feb 2022 17:50:33 GMT

thanks for your help

No it does not work

I have an error in the log: variable date not found and value is not valid

Re: proc transpose

Tom — Tue, 08 Feb 2022 17:57:55 GMT

How can HAVE be your source data? The column headers in your listing are not valid variable names. Did you accidently set the VALIDVARNAME option to ANY?

Remember that if you don't use a VAR statement in PROC TRANSPOSE then it will transpose all of the NUMERIC variables. So if you want to transpose those character variables you need to include a VAR statement.

options validvarname=any;
data have;
  input secid ('20020201'n '20020301'n '20020401'n) (:$10.);
cards;
1         Large                   small       large
2         small                   small       large
;

proc transpose data=have 
  name=date
  out=want(rename=(col1=style)) 
;
  by secid;
  var '20'n: ;
run;

Result:

Obs    secid      date      style

 1       1      20020201    Large
 2       1      20020301    small
 3       1      20020401    large
 4       2      20020201    small
 5       2      20020301    small
 6       2      20020401    large

Re: proc transpose

sasphd — Tue, 08 Feb 2022 18:03:16 GMT

the problem is importing from excel

when I import the date will be a text $1.

I have this error from you program

ERROR: "20" is not a valid name.

thanks for your help

Re: proc transpose

Tom — Tue, 08 Feb 2022 18:12:10 GMT

If you want to use a non-standard name in code you need to use a name literal. Which is a quoted string with the letter n appended. Just like a date literal is a quoted string (with the string being something the DATE informat can read) with letter d appended.

If you are having trouble with PROC IMPORT that is a different issue or the XLSX libname engine then that is a different issue.

Re: proc transpose

sasphd — Tue, 08 Feb 2022 18:57:07 GMT

I have this error in the log when importing

OTE: One or more variables were converted because the data type is not supported by the V9 engine.
For more details, run with options MSGLEVEL=I.

Re: proc transpose

Tom — Tue, 08 Feb 2022 19:08:27 GMT

That is not an error. That is SAS just being stupid. A SAS dataset only have two types of variables, floating point numbers and fixed length character strings. For some reason SAS is trying to tell you that it interpreted the character strings in your EXCEL file as variable length strings (VARCHAR type) but it will create fixed length stings (CHAR type) in the SAS dataset. As if it could do anything else.

Re: proc transpose

sasphd — Tue, 08 Feb 2022 19:27:40 GMT

but the proc transpose does not work 😥

Re: proc transpose

Tom — Tue, 08 Feb 2022 19:43:13 GMT

You need to provide more relevant information.

What does the source dataset you get from the EXCEL file actually look like?

Run PROC CONTENTS on it. Print a few lines.

What does the SAS log tell you happened?

Show the lines from the SAS log for both the step that is creating the original file and the PROC TRANSPOSE step.

When sharing information on this site make sure to copy the text as actual text and not photographs. Use the Insert Code or Insert SAS Code buttons in the forum so you can get a pop-up window where you can paste and/or edit the text you want to share.

Re: proc transpose

Reeza — Tue, 08 Feb 2022 20:02:08 GMT

Please post the code and log.

Re: proc transpose

sasphd — Wed, 09 Feb 2022 01:28:23 GMT

the log give this

  Physical Name: C:\MaRecherche\IPO\data_full_sample
38   proc import datafile = 'C:\MaRecherche\IPO\data_full_sample\PMS_data_MS_Global.xlsx' DBMS = xlsx
38 ! OUT = Global_MS ; options msglevel=i; sheet="Monthly_equity_style_box";
39

NOTE: VARCHAR data type is not supported by the V9 engine. Variable SecId has been converted to CHAR
      data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 19840101 has been converted to
      CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 19840201 has been converted to
      CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 19840301 has been converted to
      CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 19840401 has been converted to
      CHAR data type.
NOTE: VARCHAR data type is not supported by the V9 engine. Variable 19840501 has been converted to

proc transpose does not give any error in the log just the file is empty

proc transpose data=Global_MS out=_7_Morningstar_Data  ;
by Secid;
run;

91   /****** TRANSPOSER LA TABLE  **********/
92   proc transpose data=Global_MS out=_7_Morningstar_Data  ;
93   by Secid;
94   run;

NOTE: No variables to transpose.
NOTE: There were 27177 observations read from the data set WORK.GLOBAL_MS.
NOTE: The data set WORK._7_MORNINGSTAR_DATA has 0 observations and 3 variables.
NOTE: PROCEDURE TRANSPOSE used (Total process time):
      real time           0.11 seconds
      cpu time            0.06 seconds

when I run proc contents I have

Alphabetic List of Variables and Attributes 
# Variable Type Len Format Informat Label 
2 30682 Char 1 $1. $1. 30682 
3 30713 Char 1 $1. $1. 30713 
4 30742 Char 1 $1. $1. 30742 
5 30773 Char 9 $9. $9. 30773 
6 30803 Char 9 $9. $9. 30803 
7 30834 Char 11 $11. $11. 30834 
8 30864 Char 1 $1. $1. 30864 
9 30895 Char 1 $1. $1. 30895 
10 30926 Char 11 $11. $11. 30926 
11 30956 Char 11 $11. $11. 30956 
12 30987 Char 9 $9. $9. 30987 
13 31017 Char 11 $11. $11. 31017

Re: proc transpose

sasphd — Wed, 09 Feb 2022 01:32:53 GMT

please see message above

Re: proc transpose

Tom — Wed, 09 Feb 2022 01:59:19 GMT

We answered that question already. If you do not include a VAR statement then PROC TRANPOSE will only transpose the NUMERIC variables. You have ZERO numeric variables and you did not include a VAR statement. So you asked PROC TRANSPOSE to do NOTHING. So it did nothing.

Another thing you can tell from what you posted is that your column headers in your spreadsheet have Excel DATE values instead of NAMES. When SAS sees a NUMERIC value (like SAS EXCEL will store dates as NUMBERS) in a cell that it is interpreting as character (again, variable names are character strings) then it uses the character representation of the actual number being in the cell and ignores any formatting Excel might be using to display the number on the screen.

That is why your variables are named 30682 and 30682 etc instead of 19840101 and 19840201 etc. (This actually shows another place where PROC IMPORT is giving confusing messages. In the NOTE it is calling the variable '19840101' but it looks like the variable is really named '30682'.)

In your case we can deal with that AFTER you have transposed the data to get the date out of the metadata (variable name) and into actual data (value of a variable).

Your listing of the contents is not showing the name for the first variables (VARNUM=1).

Let's assume it is actually named SECID, like in your code attempt and you just didn't get that far in the lines you copied.

One way to make sure all of the variables are transposed is to use the _ALL_ variable list in the VAR statement. Note that this will also include the BY variable, but we can deal with that later when we fix the DATE values.

proc transpose data=Global_MS 
  out=step1 
;
  by Secid;
  var _all_;
run;

Now you should get something. So if your original dataset had 27,177 observations and at least 13 variables then your new dataset should have at least 13 * 27,177 observations;

Now let's fix it up a little. Let's convert the strings in the _NAME_ variable that PROC TRANSPOSE generated into actual DATE values. (You need to add that date constant to adjust for difference in how SAS and Excel store dates). And fix the name of the COL1 variable PROC TRANSPOSE will create.

data _7_Morningstar_Data;
  set step1;
  if upcase(_name_)='SECID' then delete;
  date = input(_name_,32.) + '30dec1899'd ;
  style=col1;
  format date yymmdd10.;
  drop _name_ col1;
run;