BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Nasser_DRMCP
Lapis Lazuli | Level 10
via batch.PNGvia EG.PNGproc transpose data= TAB_AMO_SORT out= TAB_AMO_SORT_TRSPS (drop=_name_)  ;
by MOIS_AMO ;
var MNT_MOIS_AMO_NET ;
id MOIS_PROD ;
run ;

ods results off;

ODS TAGSETS.EXCELXP FILE = "&xlsfile."  style=normal  ;
ODS TAGSETS.EXCELXP
      options ( skip_space='1,0,0,0,0'
	  			embedded_titles='yes'
				sheet_interval='none'
                suppress_bylines='off' 
                width_fudge='0.75'
				sheet_name='Tableau amortissement'
				) ;
	PROC PRINT DATA = work.TAB_AMO_SORT_TRSPS NOOBS ;
	title ;
	format _numeric_ z15.6 ;
  	RUN ;
ODS TAGSETS.EXCELXP CLOSE ;

Hello,

 

 

I would like to export a dataset transposed by month into an excel file.

The issue I encounter is : by lauching the prog in sas EI, it works, "yearmonth" column has values like 201601 201602..

But with the batch mode, an underscore appears ahead yearmonth values like _201601

thanks a lot in advance for yout help

Nasser

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

SAS data sets cannot have a variable name that start with any character other than a letter or the underscore character.

 

You might try adding a variable to your TAB_AMO_SO that has the text you would like to see in the output. Then use that variable in an IDLABEL statement in Proc transpose. This will assign the text as the label for the column variables. If the column names aren't critical perhaps change:

id MOIS_PROD ;

to

idlabel MOIS_PROD ;

The variable names will be col1, col2 but should have the lables you want.

 

Add the Label option to proc print to use the label as the column heading instead of the name of the variable.

PROC PRINT DATA = work.TAB_AMO_SORT_TRSPS NOOBS  Label;

View solution in original post

3 REPLIES 3
Nasser_DRMCP
Lapis Lazuli | Level 10
not sas EI but sas EG enterprise guide
RW9
Diamond | Level 26 RW9
Diamond | Level 26

This is simply a matter of not understanding SAS and using bad habbits instilled by Excel.  Whilst Excel may be good for looking at transposed data, its really not a good data storage method, and using dates as headers is bad.

What is happening is that the proc transpose is adding an underscore as variable names cannot start with a number, however labels can contain anything, so I assume your output is using in one instance labels and in the other variable names.  To fix this I would suggest that you put the date in the label part, and keey the variable names consistent, e.g:

proc transpose data=tab_amo_sort out=tab_amo_sort_trsps (drop=_name_) ;
  by mois_amo;
  var mnt_mois_amo_net;
  idlabel mois_prod;
run ;

ods results off;

ods tagsets.excelxp file="&xlsfile." style=normal;
ods tagsets.excelxp options(skip_space='1,0,0,0,0'
                            embedded_titles='yes'
                    	    sheet_interval='none'
                            suppress_bylines='off' 
                            width_fudge='0.75'
                    	    sheet_name='Tableau amortissement');
title;

proc report data=work.tab_amo_sort_trsps nowd;
  columns _all_;
run;

ods tagsets.excelxp close;

Note the use of idlabel rather than id, and i would also use proc report rather than print as its more flexible.

ballardw
Super User

SAS data sets cannot have a variable name that start with any character other than a letter or the underscore character.

 

You might try adding a variable to your TAB_AMO_SO that has the text you would like to see in the output. Then use that variable in an IDLABEL statement in Proc transpose. This will assign the text as the label for the column variables. If the column names aren't critical perhaps change:

id MOIS_PROD ;

to

idlabel MOIS_PROD ;

The variable names will be col1, col2 but should have the lables you want.

 

Add the Label option to proc print to use the label as the column heading instead of the name of the variable.

PROC PRINT DATA = work.TAB_AMO_SORT_TRSPS NOOBS  Label;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1162 views
  • 1 like
  • 3 in conversation