BookmarkSubscribeRSS Feed
ambadi007
Quartz | Level 8

Hi Experts,

 I need to handle variable names with spaces and more than 32 char leangth .

Variable names with spaces- I need to show variable names in excel , the format my client need in this format.

 

Please let me know if there is any way

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

What you need to show then is labels not variable names.  Variable names are for programming, variable labels are for display.  Names are short, minimal characters to enable programming, labels are anything and used for people to read.  You can get your labels out to Excel in any number of ways, ods tagsets.excelxp and proc report is my preferred.  As you haven't shown anything about what you are doing, how you get your output its hard to say.

You can, and I say that very loosely as it is highly recommended not to do this, reference variable names by using named literals:

'a long variable name'n

Note the n afterwards, however again, highly not recommended.  If I saw this in my code it would be removed immediately.

ambadi007
Quartz | Level 8

I use the attached macro to export data in to an excel . The template excel will be there in one folder and will call this macro in the job for exporting

%macro exportfile(pathname=, data=); 
libname _lbxls odbc noprompt="dsn=Excel Files; dbq=&pathname"; 

proc datasets lib=_lbxls; 
delete 'Data$'n; 
run; 

proc datasets lib=_lbxls; 
delete Data; 
run; 

proc append 
base=_lbxls.Data 

data=&data; 
run; 

libname _lbxls clear; 
%mend exportfile; 


%macro exportreport(path=, name=, date=, format=, data=); 
data _NULL_; 

src=&path || &name || '.xlsx'; 
src2 = &path || &name || '.xls'; 

dest = &path || &name || ' ' || put(&date, &format) || '.xlsx'; 
dest2 = &path || &name || ' ' || put(&date, &format) || '.xls'; 

if (fileexist(src)) then 
        do; 
                call system('copy ' || quote(src) || ' ' || quote(dest)); 
                call symput('_exportreportpathname', dest); 
        end; 
 else if (fileexist(src2)) then 
        do; 
                call system('copy ' || quote(src2) || ' ' || quote(dest2)); 
                call symput('_exportreportpathname', dest2); 
        end; 

run; 

%exportfile(pathname=&_exportreportpathname., data=&data); 
%mend exportreport;
RW9
Diamond | Level 26 RW9
Diamond | Level 26

If you already have a template excel file which your sending data to, why do you need variable names/labels, why are they not already in this template Excel file?

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
  • 3425 views
  • 0 likes
  • 2 in conversation