First row to Colums names

[Planck]

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

[ballardw]

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]

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]

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]

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]

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 · Accepted solution]

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]

Thank you, it was this.

[SASnewbie21]

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!