topic Re: Import variable names from excel and data for the same from notepad in SAS Programming

Import variable names from excel and data for the same from notepad

R_Win — Tue, 19 Jan 2010 12:05:36 GMT

Hi,
How to import descriptor portion (Variables names ) from Excel and the corresponding data from text file .

Regards
Sam

Re: Import variable names from excel and data for the same from notepad

DanielSantos — Tue, 19 Jan 2010 14:19:54 GMT

Sam.

Please be more specific.

What are you trying to do? Where is your data?

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Import variable names from excel and data for the same from notepad

LinusH — Tue, 19 Jan 2010 14:52:00 GMT

Well, yes.
Use PROC IMPORT on your Excel-file, and then read your text-file with appropriate method,
and PROC APPEND to table created by PROC IMPORT.
/Linus

Re: Import variable names from excel and data for the same from notepad

R_Win — Wed, 20 Jan 2010 05:27:44 GMT

Hi

I am having variables in Excel sheet and the dataset was created and i have the data in txt file now i want to create the dataset with variables in excel and data in csv.

Excel Variable:
name age

Data in Text.
sas,23
bi,45
Nani,90
kool,89

output should be

name age
sas 23
bi 45
Nani 90
kool 89

Re: Import variable names from excel and data for the same from notepad

DanielSantos — Wed, 20 Jan 2010 08:35:12 GMT

OK.

Then keeping it simple, I would go as Linus suggested.

PROC IMPORT form EXCEL (be sure to load the first row as an header row)

Then you could maybe load the table layout (var names) into a macro var list, say like this:

proc sql noprint;
select NAME into :VARNAMES separated by ',' from DICTIONARY.COLUMNS
where LIBNAME eq ' MEMNAME eq ''
order by NPOS;
quit;

After that, and assuming your data file is not extensive, build a second data file with an header row and the concatenated data.

data _null_;
infile '';
file '';
if _N_ eq 1 then put "&VARNAMES"; * put header row;
input; * read one row;
put _infile_; * write read buffer to second file;
run;

Then PROC IMPORT the resulting second file, loading the first row as an header row.

SAS will try to guess the size and type of each column from the first 20 rows (you can modify this number through the registry) in the file.

Cheers from Portugal.

Daniel Santos @ www.cgd.pt

Re: Import variable names from excel and data for the same from notepad

Peter_C — Wed, 20 Jan 2010 13:14:24 GMT

on a recent project, we got a bit - beyond simple - a lot more specific
In the excel sheet metadata was maintained, providing name and type - as defined by storage length and/or informat. An optional format could be defined. The expected column header was also defined.
The order of metadata was the output column order.
A match between column header and "expected column header" was made. Unexpected columns would be "read" as $20.
It was designed to be an iterative process to support new data. It was built because the delivered information had "creep" as the use of underlying data columns adapted and new columns were added.
It overcame a few problems in the original "excel data collection".
1 mixed type columns
2 special missing values ( cells with symbols allied to special conditions instead of numbers)
3 columns delivered don't always match the columns required list.
The data was received as a formated excel sheet. It was "unformatted" (all columns given "format=text"). Then the data were saved from excel as a plain csv file. It is this csv file which is loaded subject to the rules of the metadata.
While the metadata can be maintained in a "quality" that is easy and reliable for SAS to read from excel (with just a libname statement making the connection), the primary data received, were not able to be controlled in such a reliable way. Hence the elaborate import management.

Of course once we thought we had a reliable method for handling almost any data supplied, new complexities demonstrated its shortcomings: sometimes column headers are not unique ("which metadata row should we choose?"); data sometimes needed more than one row of column header to define it.
Basically, these problems are caused by data that does not conform to "standard csv" but that is the nature of "data in excel".
At least "excel data" never causes a "problem of volume" 😉

PeterC