DATA Step, Macro, Functions and more

First row to Colums names

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

First row to Colums names

[ Edited ]

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


Accepted Solutions
Solution
‎01-16-2017 06:35 AM
Super Contributor
Posts: 474

Re: First row to Colums names

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


All Replies
Super User
Posts: 11,343

Re: First row to Colums names

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.

 

Super Contributor
Posts: 474

Re: First row to Colums names

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

Contributor
Posts: 45

Re: First row to Colums names

[ Edited ]

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..

Super User
Posts: 11,343

Re: First row to Colums names

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.

 

 

 

Contributor
Posts: 45

Re: First row to Colums names

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

 

 

 

Solution
‎01-16-2017 06:35 AM
Super Contributor
Posts: 474

Re: First row to Colums names

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

Contributor
Posts: 45

Re: First row to Colums names

Posted in reply to DanielSantos

Thank you, it was this.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 771 views
  • 1 like
  • 3 in conversation