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)?
@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.
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 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?
@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?
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?
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: