topic one dynamic field in proc import in SAS Procedures

one dynamic field in proc import

podarum — Thu, 08 Mar 2012 14:05:24 GMT

Hi, I'm trying to figure out a way to deal with a filed from a monthly file that I have to import that is dynamic. What I mean is the file is the same every month except one field changes name.. out of 6 fileds there is a field wiht the name as a date (that changes every month).. for example from _0111130 (eg. Nov 30, 2011) to _0111231 (eg. Dec 31, 2012).. and I want it to always name it 'Pairs'..... any way of letting sas know that no matter what that field is called to always name it 'Pairs' ? thanks

one dynamic field in proc import

art297 — Thu, 08 Mar 2012 14:18:27 GMT

Is it the only field that begins with an underscore?

one dynamic field in proc import

podarum — Thu, 08 Mar 2012 14:22:58 GMT

No, there's 4 others.

one dynamic field in proc import

art297 — Thu, 08 Mar 2012 14:25:03 GMT

Is it the only field that begins with an underscore followed by a number?

one dynamic field in proc import

podarum — Thu, 08 Mar 2012 14:29:24 GMT

No, they all do..

_01

_0110831

_0111231

Re: one dynamic field in proc import

Tom — Thu, 08 Mar 2012 14:30:07 GMT

What are you importing from? Do the fields change order? If not then see if you can import without the names and then just rename the automatic variables.

Otherwise query the metadata to find the name of the field that is NOT one of the five known field names.

proc sql noprint ;

select name into :fieldnm

from dictionary.columns

where libname='WORK' and memname='IMPORT'

and name not in ('ONE','TWO','THREE','FOUR','FIVE')

;

quit;

data want ;

set import(rename=(&fieldnm=SIX));

run;

one dynamic field in proc import

podarum — Thu, 08 Mar 2012 14:32:42 GMT

From a .txt file .. the fields will not change order just value

one dynamic field in proc import

Tom — Thu, 08 Mar 2012 14:37:27 GMT

If it is a text file with known variable order why would you use IMPORT to begin with?

Use the FIRSTOBS option on the INFILE statement to skip the column headers.

data want ;

infile 'myfile.txt' firstobs=2 dsd dlm=',' truncover ;

input one two three four five six;

one dynamic field in proc import

art297 — Thu, 08 Mar 2012 15:09:50 GMT

I agree with Tom. However, since I just posted the following as a possible answer to a related question, thought you might want to know about it if you have ALREADY imported all of the data and have SAS files.

libname mydata "c:\art\test";

data mydata.productid;

set sashelp.class (rename=(sex=productid));

run;

data mydata.salesamount;

set sashelp.class (rename=(sex=salesamount));

run;

data _null_;

dsid=open("mydata.productid","i");

call execute('proc datasets library=mydata nolist;');

call execute(' modify productid;');

call execute(' rename '||varname(dsid,2)||'=errorcolumn;');

call execute('quit;');

rc=close(dsid);

dsid=open("mydata.salesamount","i");

call execute('proc datasets library=mydata nolist;');

call execute(' modify salesamount;');

call execute(' rename '||varname(dsid,2)||'=errorcolumn;');

call execute('quit;');

rc=close(dsid);

run;

one dynamic field in proc import

podarum — Thu, 08 Mar 2012 15:23:50 GMT

Thanks guys.