Help using Base SAS procedures

one dynamic field in proc import

Reply
Super Contributor
Posts: 396

one dynamic field in proc import

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

PROC Star
Posts: 7,365

one dynamic field in proc import

Is it the only field that begins with an underscore?

Super Contributor
Posts: 396

one dynamic field in proc import

No, there's 4 others.

PROC Star
Posts: 7,365

one dynamic field in proc import

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

Super Contributor
Posts: 396

one dynamic field in proc import

No, they all do..

_01

_0110831

_0111231

_1

Super User
Super User
Posts: 6,502

Re: one dynamic field in proc import

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;

Super Contributor
Posts: 396

one dynamic field in proc import

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

Super User
Super User
Posts: 6,502

one dynamic field in proc import

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;

PROC Star
Posts: 7,365

one dynamic field in proc import

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;

Super Contributor
Posts: 396

one dynamic field in proc import

Thanks guys.

Ask a Question
Discussion stats
  • 9 replies
  • 220 views
  • 6 likes
  • 3 in conversation