03-21-2016 03:01 PM
I have the following spreadsheet I want to import. During the import stage I also want to Rename, Retain and Label the column. Is it possible to implement all three functions during the import stage? Thanks
ID Name1 Name2 Title1 Title2
1234 Alex Michael 23 New York St. (123) 563-2541
5678 John Jacob 202-74th St. 123-585-4785
3456 Heather-Bin Tornoway 220- 35th Ave. (123)4587458
2345 Megan-Fitzam Hansen 9 Huron st. (123)4587458
I came up with the following code:
proc import datafile="C:\Have.xlsx"
out=Want (RENAME=(ID=St_ID Name1=First_name Name2=Last_name Title1=Address) keep = ID Name1 Name2 Title1);
St_ID First_name Last_name Address
1234 Alex Michael 23 New York St.
5678 John Jacob 202-74th St.
3456 Heather-Bin Tornoway 220- 35th Ave.
2345 Megan-Fitzam Hansen 9 Huron st.
03-21-2016 03:12 PM - edited 03-21-2016 03:16 PM
Save the spreadsheet as a CSV file. Use Proc import to generate a basic datastep to read the data, copy the code from the log and add any changes desired. For instance to a search and replace on the default variable names to the name you want. Add label code.
I'm not quite sure what you want to "retain" but since this is a data step you can any datastep code you desire.
If you are happy with the contents of your data and just need to change names and add labels then Proc Datasets is the tool designed to do both to a set in place without re-reading any data or creating an additional dataset.
03-21-2016 03:47 PM
03-21-2016 04:15 PM
After renaming the dataset during import stage, it still keep the LABEL as it was in the excel spreadsheet. I wanted to keep label same as my new variable name (after renaming).
Are you saying that the LABEL statement in PROC IMPORT is not effective? (I cannot test it with dbms=xlsx, unfortunately, because I don't have Excel installed, but it works with dbms=dlm.)
Also, wouldn't labels equal to the variable names add only little value since in most cases variable names would be used anyway by default for display if no labels were present (so you could simply remove all labels)?
As to the order of variables in the dataset (which is rarely relevant), I would be surprised if this could be arranged with PROC IMPORT alone. No problem, of course, with a data step reading the raw data.
03-21-2016 04:40 PM
Need further help from the community? Please ask a new question.