DATA Step, Macro, Functions and more

Retain and Label during Data Import stage

Accepted Solution Solved
Reply
Regular Contributor
Posts: 215
Accepted Solution

Retain and Label during Data Import stage

Hi All,

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
;
run;

 

I came up with the following code:

 

proc import datafile="C:\Have.xlsx"
dbms=xlsx replace
out=Want (RENAME=(ID=St_ID Name1=First_name Name2=Last_name Title1=Address) keep = ID Name1 Name2 Title1);
sheet='Sheet1';
run;

 

Expected Output:

 

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.            


Accepted Solutions
Solution
‎03-22-2016 11:36 AM
Trusted Advisor
Posts: 1,115

Re: Retain and Label during Data Import stage

Use the following statement (it works even in my PROC IMPORT step):

attrib _all_ label=' ';

View solution in original post


All Replies
Super User
Posts: 10,483

Re: Retain and Label during Data Import stage

[ Edited ]

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.

Regular Contributor
Posts: 215

Re: Retain and Label during Data Import stage

Hi ballardw, by retain I want to rearange the variables (columns).
Regular Contributor
Posts: 215

Re: Retain and Label during Data Import stage

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).
Trusted Advisor
Posts: 1,115

Re: Retain and Label during Data Import stage


mlogan wrote:
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.

Regular Contributor
Posts: 215

Re: Retain and Label during Data Import stage

How do I remove all labels from the sas dataset?
Solution
‎03-22-2016 11:36 AM
Trusted Advisor
Posts: 1,115

Re: Retain and Label during Data Import stage

Use the following statement (it works even in my PROC IMPORT step):

attrib _all_ label=' ';
Regular Contributor
Posts: 215

Re: Retain and Label during Data Import stage

Where in import step you put that in, would you please show it ihere:

proc import datafile="C:\Have.xlsx"
dbms=xlsx replace
out=Want (RENAME=(ID=St_ID Name1=First_name Name2=Last_name Title1=Address) keep = ID Name1 Name2 Title1);
sheet='Sheet1';
run;
Trusted Advisor
Posts: 1,115

Re: Retain and Label during Data Import stage

I'd put it just before the RUN statement.

Regular Contributor
Posts: 215

Re: Retain and Label during Data Import stage

Thanks Reinhard, It worked for me. The 'Attrib' was showing up with red color and I thought SAS is not accepting itSmiley Happy Thanks for the help.
Trusted Advisor
Posts: 1,115

Re: Retain and Label during Data Import stage

Glad to hear that it worked. I was also surprised by the incorrect syntax highlighting in this case.

Regular Contributor
Posts: 215

Re: Retain and Label during Data Import stage

Not working right before RUN, tried other places too. have you tried from your end? Thanks,
Trusted Advisor
Posts: 1,115

Re: Retain and Label during Data Import stage

I have it right before RUN. Do you receive error messages or in which way does your step fail?

Trusted Advisor
Posts: 1,115

Re: Retain and Label during Data Import stage

Hi @mlogan,

 

You can also insert a LABEL statement into the PROC IMPORT step:

label St_ID      = 'Student ID'
      First_name = 'First name'
/* ... */
;
Regular Contributor
Posts: 215

Re: Retain and Label during Data Import stage

I wanted to keep label same as my new variable name (after renaming).
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 14 replies
  • 372 views
  • 2 likes
  • 3 in conversation