topic first observation is variable name in SAS Data Management

first observation is variable name

pmpradhan — Sat, 10 Nov 2018 16:37:29 GMT

Currently, My data is like:

A	B	C	D
Name	Sex	Address	Phone
Rachel	F	789 Mid County	111-111-1111

My first observation is my variable name. therefore, I want my data to look like:

Name	Sex	Address	Phone
Rachel	F	789 Mid County	111-111-1111

Please help!

Re: first observation is variable name

novinosrin — Sat, 10 Nov 2018 17:02:29 GMT

Is current sas dataset?

excel or some other like csv?

Re: first observation is variable name

pmpradhan — Sat, 10 Nov 2018 17:07:40 GMT

Yes it is SAS dataset.

Re: first observation is variable name

VDD — Sat, 10 Nov 2018 17:15:13 GMT

Sorry to say that is a poor data table. Looks like the solution should have been handled during the creating of the original table. Why would anyone create a table in that manner in SAS.

Re: first observation is variable name

pmpradhan — Sat, 10 Nov 2018 17:24:02 GMT

Agree, One of the simple way, I found is renaming all vars then deleting first observation. I was hoping to see if others find a different way.

Re: first observation is variable name

novinosrin — Sat, 10 Nov 2018 17:27:32 GMT

Well done, i think your approach is pretty sound!

Re: first observation is variable name

mkeintz — Sat, 10 Nov 2018 17:33:02 GMT

So you have a sas dataset with (1) var names of A, B, C, ..., (2) the intended var names are actually the values of A, B, C, ... in the first row. Looks like it was once a spreadsheet.

Is the spreadsheet still available? If so, use proc import.

Re: first observation is variable name

pmpradhan — Sat, 10 Nov 2018 17:33:45 GMT

Unfortunately not.

Re: first observation is variable name

mkeintz — Sat, 10 Nov 2018 17:43:27 GMT

Establish a temporary file for holding renames
Read the first obs
Write to #1 above using a "put (_all_) (=)" statement to write a set of name=value pairs, where the names will be A, B, C, D and the values will be the content in the first obs.
Run a second data step with (firstobs=2), and include the renames written above

data have;
  input A $6. +1 B $3. +1 C $14.  +1  D $12.;
datalines;
Name   Sex Address        Phone 
Rachel F   789 Mid County 111-111-1111 
run;

filename renames temp ;

data _null_;
  set have (obs=1);
  file renames;
  put 'rename '
    / (_all_) (=)
	/ ';'
	;
run;

data want;
  set have (firstobs=2);
  %include renames ;
run;

Re: first observation is variable name

tocilj — Sat, 04 Jul 2020 20:44:18 GMT

Outdated reply but may help someone in future

/*----------------------------------------*/
data test;
infile datalines;
input A : $10. B : $3. C & $20. D : $20.;
datalines;
Name Sex Address Phone
Rachel F 789 Mid County 111-111-1111
;
run;

proc transpose data=test out=testt;
var A B C D;
run;

proc transpose data=testt out=testtt(drop=_:);
var col2;
id col1;
run;
/*----------------------------------------*/

Re: first observation is variable name

mkeintz — Mon, 06 Jul 2020 16:21:18 GMT

@tocilj

The double proc transpose is nice in that it has a simple structure, and it uses a pre-packaged sas routine.

But it may not scale well. Transposing 1,000,000 observations will create a dataset with 1,000,000 variables in the intermediate data set TEST1. The entire dataset would have to be processed two times, which can begin to use significant resources.