topic Re: How to handle variable names with spaces and more than 32 leangth in SAS Programming

How to handle variable names with spaces and more than 32 leangth

ambadi007 — Wed, 08 Feb 2017 10:10:24 GMT

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

Re: How to handle variable names with spaces and more than 32 leangth

RW9 — Wed, 08 Feb 2017 10:29:20 GMT

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.

Re: How to handle variable names with spaces and more than 32 leangth

ambadi007 — Wed, 08 Feb 2017 11:45:47 GMT

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;

Re: How to handle variable names with spaces and more than 32 leangth

RW9 — Wed, 08 Feb 2017 11:48:18 GMT

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?