topic Re: Datastep Input for Variable Names in SAS Programming

Datastep Input for Variable Names

deleted_user — Mon, 29 Sep 2008 21:36:48 GMT

I'm using a datastep to import a csv into SAS. The data needs some cleansing so I'm unable to get the data in through proc import. But I really want to use a feature that is used in proc import: Not knowing the variable names or number of variables in advance. Is there an option that allows the first line of the input command to serve as variable names? or is there a special command ie: vinput that allows you to read in the variable names?

thanks
johnny

Re: Datastep Input for Variable Names

sbb — Mon, 29 Sep 2008 23:46:19 GMT

It would be most useful for you to share an example of your challenging input data and how you would like it to be interpreted by SAS.

Also, you mention "...option that allows the first line of the input command to serve as variable names?" Can you explain "input command" -- did you mean input file? If so, there is a "PROC IMPORT" parameter you can code to tell SAS to use the first data row as the variable names.

In addition to hosting the SAS product documentation, there are numerous SAS technical papers on all sorts of topics, available at the SAS support website http://support.sas.com . I did a search and found the SAS technical reference below on PROC IMPORT, along with the DOC links provided.

Scott Barry
SBBWorks, Inc.

SAS Technical document on PROC IMPORT:
ftp://ftp.sas.com/techsup/download/sample/datastep/import.html

SAS PROC IMPORT DOC on Data Source statements:
http://support.sas.com/documentation/cdl/en/proc/59565/HTML/default/a000312413.htm

SAS Support website DOC page:
http://support.sas.com/documentation/onlinedoc/base/index.html

Re: Datastep Input for Variable Names

deleted_user — Tue, 30 Sep 2008 03:58:40 GMT

why not import first then doing the cleaning,
or you can use some macro, or functions such as varname() varn()(may not exactly , you can check in help)

Re: Datastep Input for Variable Names

Patrick — Tue, 30 Sep 2008 12:02:28 GMT

I was searching for some kind of %csv2ds macro - but couldn't find anything. I'm almost sure searching with some more patiance would bring a result.

If you want to solve it by yourself then you'll end up in some serious programming.
It's not only about getting the names for variables, it's also about getting the type and length - and this means first scanning the first x rows and see what data you've got (proc import has also to do something like that).
Once you've got that all you could build a macro variable with the necessary input statement and then just use this macrovar in a dataset, i.e:

data readall;
input &inputlist;
....

But as I said: Creating the value for this macrovar means some programming. Better use proc import and cleanse/recode the variables afterwards.

HTH
Patrick

Re: Datastep Input for Variable Names

deleted_user — Tue, 30 Sep 2008 23:05:06 GMT

Thanks for everyone's input.

To answer a couple of your questions:

sbb-
>>>"...option that allows the first line of the input command to serve as variable names."
<<<"...did you mean input file."
I didn't really mean input file but yes the first line of the input file is used by proc import, read in, stored as variable names, and assigns the datatype and length based on the second line of the input file. when I run proc import my log produces the "underlying" datastep...with all of the variable names. how does it get them?

I'd love to use proc import then do the cleansing the difficulty is that the input file has 8 lines of header information 3 blank lines, 1 line for data title (inventory) then a data table, including variable names and ended with a TOTAL line. Then there are 3 more blank lines another line for data table title (cost) and another data table. This continues on and on for about 15 variables...yeah 15 data tables in one csv. There is too much going on in the file for proc import to handle the data correctly...as far as I know. I'd like each of the 15 data tables in the CSV to become a seperate and uniquely named dataset in SAS...as you'll see in the code below.

Patrick - you nailed my issue. If the first line of data I import is the variable name it sets my variable type and length to strings of that length...which is not at all what i need. Then since I do not know the name of those variables (since each table is different) I'm unable to attempt data transformations.

lastly, I imagine with some extensive macro programming and looping I could power this out..as you can see I've already done some of below...but I try to work under the premise that if I have to work hard to do something that is easy (ie for proc import) then I probably don't know something that I should.

here is the code that is currently importing my data.

data outlib.&MakeSet;
length RowNav $ 50;
drop Count;
infile %UNQUOTE(&InFilePath&InFileName&InFileIndex&CSV) dlm=','
missover lrecl=1000;
input RowNav; *row navigation variable indicating where I'm at in the file;
if RowNav=%UNQUOTE(&&Trigger&MakeSet) then do; *macro triggers based
data title;
array Temp{&NumMonths} $25.;
do until (RowNav = &&End&MakeSet); *macro trigger for TOTAL line (last row);
input RowNav $ @;
do Count=1 to &NumMonths-1; *previously determined number of columns;
input Temp(Count) @;
if Temp(Count) = '' then Temp(Count) = 0;
end;
input Temp(&NumMonths);
output;
end;
end;
else delete;
run;

Again thanks for everyone's input...i'm looking into your suggestions.
johnny

Re: Datastep Input for Variable Names

Cynthia_sas — Wed, 01 Oct 2008 18:54:50 GMT

Hi:
Before you try to reinvent the wheel, this Tech support note (found by searching support.sas.com)
http://support.sas.com/kb/13/735.html

... describes a program called rangeimporter.sas, which, according to the note:
"The rangeimporter9.sas program can import ranges of Excel data that do not include the first row or column and retain the column names. "

Maybe that program can help you do what you want to do.

cynthia

Re: Datastep Input for Variable Names

deleted_user — Wed, 01 Oct 2008 19:59:34 GMT

Cynthia, awesome...I'll check it out. Thanks as always.

Re: Datastep Input for Variable Names

Patrick — Thu, 02 Oct 2008 12:01:43 GMT

Hi Johnny
After your clarifications I think what you're looking for is solved within the current Proc Import (it used to be different). Have a look at: http://support.sas.com/onlinedoc/913/getDoc/en/proc.hlp/a000312413.htm
especially "Getnames" and "Guessingrows"
HTH
Patrick