BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Planck
Obsidian | Level 7

Hi,

 

During the proc import I asked for gatnames = no because I had to work on the file first.

Now in a data step I would like to transform the table and to do it.

Something like:

data Table2;
set Table1;
getnames = yes;
run;

How can I do it easily?


Thank you for your help

1 ACCEPTED SOLUTION

Accepted Solutions
DanielSantos
Barite | Level 11

Hi.

 

From your code, I would say you are very near.

 

Your SQL statement should look like this:

 

PROC SQL noprint;
SELECT cats(cats(_name_,' = '),col1) 
INTO :rename separated by ' '
FROM names;
quit;

 

The INTO: statement is a special SQL statement to create a macro variable based on the result of a query, not for data manipulation or creating tables.

 

Check this great paper of Thiru Satchi about the INTO: statement: http://www2.sas.com/proceedings/sugi27/p071-27.pdf

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

View solution in original post

8 REPLIES 8
ballardw
Super User

No, getnames is only a Proc Import option and limited at that. You change existing names with Proc Datasets modify or  RENAME statement in a data step or with the rename data set option.

 

What does "I add to work on the file first" mean? I am not sure what "work" would be done on a file that means you don't want the variable names.

What type of file were you importing? I think you need to provide some further details on what you are attemtpting to do.

 

DanielSantos
Barite | Level 11

Hi.

 

I'm sorry but I didn't get it... could be me.

 

You skipped the first row while importing the file into a table1 (for some reason), thus not using the variable names from the title row of file.

 

But after that you still want to name the variables of table2 (which is table1 transformed) from the title row of the file?

 

Daniel Santos @ www.cgd.pt

Planck
Obsidian | Level 7

Sorry,

 

I made a mistake when typing. It is of course "I had to work on the file first".

 

I couldn't get names while importing because the file was very messy.

 

So I imported the file directly, and worked on it. Now it is tidy in Table1, with the first row corresponding to the column names.

I need to get column names now with the first row.

 And I want to transform Table1 on Table2, with the same data but with good column names..

ballardw
Super User

Did you actually add values to the data set? The first row of data should not be column names. Or did you use one of the tools to change the names such as the column view and then retyped variable names?

 

I think that you may want to run Proc Contents on your data set and post the result here as your descriptions aren't very clear.

 

And may be some example data. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to generate data step code from an existing data set that will show us exactly what you have in your table1. The maine part would be the informat in input statements and a few lines of the actual data. The resulting text should be pasted into a code box opened using the {i} icon in the forum menu.

 

 

 

Planck
Obsidian | Level 7

Hi,

 

I finally found a partial solution in order to replace column names by first row values.

 

But it is only partially working.

 

This is my code.

My table A is the one without column names, and I want to create B with the column names based on first row values.

PROC TRANSPOSE data=WORK.A(obs=1) out=names ;
  var _all_;
run;

PROC SQL;
CREATE TABLE TRANSFORM AS
  SELECT cats(cats(_name_,' = '),col1) 
    INTO :rename separated by ' '
    FROM names;
quit;
data WORK.B;
  SET A (firstobs=2 rename=(&rename));
run;

But I have a mistake at "&rename". SAS tells me:

WARNING: Apparent symbolic reference RENAME not resolved.
ERROR 214-322: Variable name & is not valid.

ERROR 23-7: Invalid value for the RENAME option.

ERROR 79-322: Expecting a =.

 

My final TRANSFORM table looks like:

 

B=CO
C=A
D=E_L_T
E=C_P
F=D
G=N
H=R
I=TOT

 

Thanks for help

 

 

 

DanielSantos
Barite | Level 11

Hi.

 

From your code, I would say you are very near.

 

Your SQL statement should look like this:

 

PROC SQL noprint;
SELECT cats(cats(_name_,' = '),col1) 
INTO :rename separated by ' '
FROM names;
quit;

 

The INTO: statement is a special SQL statement to create a macro variable based on the result of a query, not for data manipulation or creating tables.

 

Check this great paper of Thiru Satchi about the INTO: statement: http://www2.sas.com/proceedings/sugi27/p071-27.pdf

 

Hope it helps.

 

Daniel Santos @ www.cgd.pt

Planck
Obsidian | Level 7

Thank you, it was this.

SASnewbie21
Calcite | Level 5

Hi! I saw your reply to this question but I can't seem to get it work. I'm super new to SAS so that may be the reason. 

 

I have only one row which I want to use to rename the columns and then populate the data later. Its one row and 20 columns.

 

I used this code:


proc sql ;
select catx('=',_name_)
into : new_columns separated by ' '
from lib.row
;
quit;

 

The only result I get is _name_= (as a one row one column table and the _name_= is in the row with no name for the column name)

Is there something else I can do? I cant seem to find the problem.

 

 

Thank you!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 25591 views
  • 4 likes
  • 4 in conversation