topic Re: Prevent Macro from truncating character variable name when moving to Excel in New SAS User

Prevent truncating character variable name when moving to Excel

moorem30 — Wed, 14 Feb 2024 17:13:48 GMT

I am running the below code to send data to Excel but have noticed that is truncates the Name(Hospital) variable. Is there a way to keep from cutting off the name when sending to Excel? Below is the code I'm using.

%MACRO READMIT_RPT_30(HOSPITAL, HOSPITAL_ID, RPTMONTH, PAID,flag);

%PUT COPY TEMPLATE TO DIRECTORY;

%let xls_orig_30 = E:\XDrive\Payor Provider\PayorProvider(DeptUsers)\SACA\Readmission Analysis\New Process\Template\test.xls;

%let xls_dest_30 = E:\XDrive\Payor Provider\PayorProvider(DeptUsers)\SACA\Readmission Analysis\New Process\&year_folder.\&RPTMONTH_30.\&HOSPITAL._READMIT_RPT_&PAID..xls;

%put &xls_dest_30.;

options noxwait;

X copy "&xls_orig_30." "&xls_dest_30.";

%PUT DEFINE NEW TEMPLATE LOCATION;

%LET TEMPLATE = &xls_dest_30.;

%PUT &TEMPLATE.;

* DEFINE TEMPLATE FILE FOR DATA INPUT;

%PUT DEFINE TEMPLATE FILE FOR DATA INPUT;

LIBNAME READMIT EXCEL "&TEMPLATE.";

*RUN;

%PUT DELETE EXISTING DATA;

PROC DATASETS LIB = READMIT noprint;

DELETE DATA2-DATA17;

RUN;

%PUT DEFINE NEW DATA;

DATA READMIT.DATA2; SET WORK.DATA2b; WHERE SERVICE_provider_id = substr(substr(&HOSPITAL_ID,1,16),1,16) and system_flag=&flag; drop system_flag; RUN;

DATA READMIT.DATA3; SET WORK.DATA3b_30; WHERE SERVICE_provider_id = substr(&HOSPITAL_ID,1,16) and system_flag=&flag; drop system_flag; RUN;

DATA READMIT.DATA4; SET WORK.DATA4b_30; WHERE SERVICE_provider_id = substr(&HOSPITAL_ID,1,16) and system_flag=&flag; drop system_flag; RUN;

DATA READMIT.DATA5; SET WORK.DATA5b_30; WHERE SERVICE_provider_id = substr(&HOSPITAL_ID,1,16) and system_flag=&flag; drop system_flag; RUN;

DATA READMIT.DATA6; SET WORK.DATA6b_30; WHERE SERVICE_provider_id = substr(&HOSPITAL_ID,1,16) and system_flag=&flag; drop system_flag; RUN;

DATA READMIT.DATA7; SET WORK.DATA7b_30; WHERE SERVICE_provider_id = substr(&HOSPITAL_ID,1,16) and system_flag=&flag; drop system_flag; RUN;

LIBNAME READMIT CLEAR;

RUN;

%MEND READMIT_RPT_30;

Re: Prevent Macro from truncating character variable name when moving to Excel

Tom — Wed, 14 Feb 2024 16:13:31 GMT

I am sorry but it is not at all clear what NAME variable you are talking about.

Re: Prevent Macro from truncating character variable name when moving to Excel

moorem30 — Wed, 14 Feb 2024 16:17:21 GMT

My apologies, Hospital is the name

Re: Prevent Macro from truncating character variable name when moving to Excel

moorem30 — Wed, 14 Feb 2024 16:17:54 GMT

Hospital is the name

Re: Prevent Macro from truncating character variable name when moving to Excel

Reeza — Wed, 14 Feb 2024 16:43:11 GMT

And where is the hospital variable? What is the format and length? You've double checked that the variable is not truncated in the source data sets?

Re: Prevent Macro from truncating character variable name when moving to Excel

Reeza — Wed, 14 Feb 2024 16:44:00 GMT

If it's from the file name issue, it's most likely in how you call the macro which is not shown.

Re: Prevent Macro from truncating character variable name when moving to Excel

Quentin — Wed, 14 Feb 2024 16:55:05 GMT

This is not a macro issue, so I would remove the macro language from the problem statement.

If you run code without a macro like:

LIBNAME READMIT EXCEL " /*path to some excel file*/ ";

DATA READMIT.DATA2; 
   SET WORK.DATA2b;
RUN;

Does that show the problem? If so, run PROC CONTENTS and PROC FREQ on the Hospital variable in DATA2b, to see if the values are truncated, or there is a format assigned that is truncating values.

It's easier to debug SAS code than macro code.

Re: Prevent Macro from truncating character variable name when moving to Excel

moorem30 — Wed, 14 Feb 2024 20:52:53 GMT

This is showing the text length is 50 but when reading to excel, it shortens the length to less than 50

Re: Prevent Macro from truncating character variable name when moving to Excel

Tom — Wed, 14 Feb 2024 21:56:51 GMT

@moorem30 wrote:
This is showing the text length is 50 but when reading to excel, it shortens the length to less than 50

It is not clear what you mean. The LENGTH of a character variable is the MAXIMUM number of bytes it can hold. But any observations can have values that are shorter then that maximum, it just cannot have more.

Are you saying that when you WRITE the values into an Excel sheet and look at the values in the Excel sheet that some of them are shorter than 50 bytes? Note that an Excel sheet does not have anything like the concept of the LENGTH of variable. In a Excel sheet each CELL is independent can can hold anything at all.

Is there some specific value that is being truncated? So for example does a value like CALIFORNIA in the SAS variable end up as CALI in the Excel sheet?

Re: Prevent Macro from truncating character variable name when moving to Excel

Reeza — Wed, 14 Feb 2024 22:00:44 GMT

You should show an example.

Show a hospital name with the variable that is 50 characters, and the proc contents output that shows the length.

Screenshots are fine in this case.

Often what has happened in these cases is the data was truncated at an earlier point in time and although the length is increased (format increased) the data is still truncated. However, really we're just guessing without seeing the full process.

Re: Prevent Macro from truncating character variable name when moving to Excel

Quentin — Wed, 14 Feb 2024 22:49:34 GMT

Can you make a small example that demonstrates the problem, which we could try running?

For example, I ran this code:

LIBNAME READMIT EXCEL "Q:\junk\myexcel.xls";

data have ;
  var='01234567890123456789012345678901234567890123456789' ;
run ;

DATA READMIT.DATA2; 
   SET have;
RUN;

libname readmit clear ;

But it did not truncate the value written to Excel.

Since your problem is not resolved, I would recommend you undo marking my the accepted solution. I think there is a button that says "unaccept as solution" or "this is not the solution" or something like that. When you mark a question as correct/accepted, some people will stop reading the thread.

Re: Prevent Macro from truncating character variable name when moving to Excel

moorem30 — Thu, 15 Feb 2024 02:57:07 GMT

Yes, when I write the values into Excel, it is shorter than 50 characters. Yes, exactly like the California example

Re: Prevent Macro from truncating character variable name when moving to Excel

Tom — Thu, 15 Feb 2024 03:35:28 GMT

@moorem30 wrote:
Yes, when I write the values into Excel, it is shorter than 50 characters. Yes, exactly like the California example

Did you try just telling excel to widen the column?