Making sense of this macro code

[SAS_SoS123]

Hello, 

My question has two parts. I am new at my job and I am working on updating a dataset that is supposed to be updated on a monthly basis. The person who did this before me has macro code written to read in a text file and create a SAS dataset from that text file. The issue is that in order to make the text file, this person took an excel file, edited the dataset in SAS and then exported it as text, which was then read into the macro. 

My question is: 

1. Is there a way to just import the xlsx file and create a SAS dataset that can be used in the macro (so a SAS dataset in the infile statement rather than reading in the txt file)?

[PaigeMiller]

---

@SAS_SoS123 wrote:

My question is: 

1. Is there a way to just import the xlsx file and create a SAS dataset that can be used in the macro (so a SAS dataset in the infile statement rather than reading in the txt file)?

---

Yes, you can use PROC IMPORT or a LIBNAME statement to access data in Excel, but unless you provide a lot more details, we can't be more specific.

[SAS_SoS123]

The way the other person had it set up was to take an xlsx sheet, change the variable names in excel, import this into sas to then export as txt. Then this txt file was called into the macro using the infile statement. I will try to post an example of the macro edited for privacy.

%LET date = 12feb2021;

/*Read in the tab delimited .txt MASTERLIST file you create from the Masterlist
.xlsx file. (see above)*/
DATA MASTERLIST_&date.;

/*Set the ERROR detection macro variable */
%LET _EFIERR_ = 0;
INFILE "Pathway\MASTERLIST_&date..txt" delimiter='09'x
MISSOVER DSD lrecl=32767 firstobs=2*/;
INFORMAT var1 $4. id $9. var3 $9. var4 $3. var5 $3. var6 mmddyy10.
DOB_ mmddyy10. Sex_ $1. Race_ $16. Marital_ $8.;
FORMAT var1 $4. id $9. var3 $9. var4 $3. var5 $3. var6 mmddyy10.
DOB_ mmddyy10. Sex_ $1. Race_ $16. Marital_ $8.;
INPUT var1 $4. var2 $9. var3 $9. var4 $3. var5 $3. var6 mmddyy10.
DOB_ mmddyy10. Sex_ $1. Race_ $16. Marital_ $8.;
Cohort=’cases’;
Inactive='False';

/*Set ERROR detection macro variable*/
IF _ERROR_ THEN CALL symputx('_EFIERR_',1);

/*There are a lot of missing rows for some reason. So I will get rid of them*/
IF id~='';
RUN;

Does this help? Where I would be interesting in changing it would be the infile statement -- so it would just read in a SAS dataset and take out the xlsx-->SAS dataset-->txt file step.

[PaigeMiller]

Why is it necessary to rename the variables in Excel? Can't this be done in SAS? Can this step be skipped? Why is it necessary to rename the variables at all?

Why can't the process be 

Excel->SAS Data set and then use the data.

[SAS_SoS123]

I'm guessing they use the macro for the date since it is updated on a monthly basis. I was trying to follow their old way of doing it, but importing and exporting the data so much was causing variables to be reformatted and it doesn't make sense. Can I not reference another SAS dataset in the macro code?

[PaigeMiller]

---

@SAS_SoS123 wrote:
I'm guessing they use the macro for the date since it is updated on a monthly basis. I was trying to follow their old way of doing it, but importing and exporting the data so much was causing variables to be reformatted and it doesn't make sense. Can I not reference another SAS dataset in the macro code?

---

I guess I'm still not grasping any of this.

Also, you talk about macros, but you don't show any SAS macros, you only show macro variables. These are not the same. Are you referring to macro variables?

[ballardw]

---

@SAS_SoS123 wrote:
I'm guessing they use the macro for the date since it is updated on a monthly basis. I was trying to follow their old way of doing it, but importing and exporting the data so much was causing variables to be reformatted and it doesn't make sense. Can I not reference another SAS dataset in the macro code?

---

An example of the text file might be helpful.

Copy a few lines from the text file using a plain text editor and paste into a text box opened on the forum. The example should include the header row.

Read into SAS>export to Excel, edit, and re-read means someone didn't know how to use one or more features of SAS somewhere along the line.

Exactly what "date" is involved? How does it change from file to file?

[SAS_SoS123]

I can't attach any of the text file as it is health data. What happens is the excel files are pushed to our servers once a month and then they are to be read into SAS and edited. I guess my issue here is the infile statement. Does this have to be a text file or can I bring in a SAS dataset to this step? 

[Reeza]

but importing and exporting the data so much was causing variables to be reformatted and it doesn't make sense. 

When you read in an Excel file you cannot specify the types or guarantee them. So one workaround is to export the file to a text file so you can specify the types and formats explicitly. This ensures the rest of your code will work as the types are what you expect. This is especially true if the file structure and column orders are changing from month to month.