topic Re: Proc import in SAS Procedures

Proc import

jayanth_sas — Sun, 27 Oct 2013 09:07:18 GMT

I have a flat file of 1000 observations and 50 variables. I can able to directly extract this by proc import procedure but how can we assign formats to this data? Do we have any thing while importing can we assign length, data type, label? Can any one suggest me on this plz.

Re: Proc import

Jagadishkatam — Sun, 27 Oct 2013 12:58:54 GMT

you can try to import the data using the data step instead of the proc import procedure, incase if you wish to control the formats, length etc., of the variables.

Something like below,

data want;

infile "~path/test.csv";

informat ....;

format .......;

input variables name .............;

run;

Thanks,

Jagadish

Re: Proc import

art297 — Sun, 27 Oct 2013 14:01:21 GMT

Immediately after running proc import press function key F4. That will give you the code that was run and you can modify all of the formats and informats as desired and then rerun the modified code.

Re: Proc import

Jagadishkatam — Sun, 27 Oct 2013 14:16:26 GMT

Hi Sir,

I tried to import a sample data by proc import in sas 9.2 and after execution by default in the log it displayed the back end datastep it executed for proc import.

So probably for sas version less than 9.2 we need to use the function f4 to get the same.

Thanks,

Jagadish

Re: Proc import

art297 — Sun, 27 Oct 2013 14:32:02 GMT

It is the same with all of the SAS versions. The log displays the code that was run, but pressing function key f4 inserts that code into your editor.

Re: Proc import

Jagadishkatam — Sun, 27 Oct 2013 14:38:21 GMT

Thank you Sir, i just noticed it.

Re: Proc import

jayanth_sas — Sun, 27 Oct 2013 16:07:36 GMT

Hi jagadish,

Yeah we can do the same in infile if you have few variables, if variable list is 100 , 200 then using infile is tedious na.

Re: Proc import

jayanth_sas — Sun, 27 Oct 2013 16:09:37 GMT

Hi sir Arthur,

Thank you very much for your advice let me try this and come back to you.

Re: Proc import

art297 — Sun, 27 Oct 2013 21:18:19 GMT

Of Note: Don't expect the proc generated code to be optimal, well written code. IT WON'T BE! When you press F4 you will get the exact code that the proc submits, but the proc-generated code has numerous macro variables and checks that don't have to be there, and uses separate format and informat statements for every variable. Obviously, getting rid of the fluff and taking advantage of variable lists can make the code a lot easier to maintain.

Re: Proc import

Tal — Tue, 29 Oct 2013 03:18:35 GMT

Hi Arthur,'

so if i importing a file

proc import datafile='C:\Users\x\Desktop\SAS Files\test.xls' out=new replace;
run;

and after running this code when i pres the F4 i get the same code as above

Can you please explaing this below ?

"The log displays the code that was run, but pressing function key f4 inserts that code into your editor."

Re: Proc import

art297 — Tue, 29 Oct 2013 04:24:56 GMT

Ah, this is the first time (that I know of at least) that you mentioned you were importing an Excel workbook. All of my suggestions were based on the (apparently wrong) assumption that you were importing a text or delimited file.

Re: Proc import

jayanth_sas — Tue, 29 Oct 2013 08:46:39 GMT

Hi arthur,

The abobe comment is passed by some one (Tal) else and the suggestions you have provided earlier is for the correct purpose only i.e text or delimited file.

Thank you.

Re: Proc import

Tal — Tue, 29 Oct 2013 17:46:14 GMT

sorry about the confusion Arthur. So the F4 key function works only with text files and does the import data wizard has the ability of changing format as you import the file,probably not?

Re: Proc import

art297 — Tue, 29 Oct 2013 18:01:10 GMT

Tal: You really ought to start a new discussion rather than trying to piggyback onto an existing one.

However, while I don't think proc import lets you control formats and informats, the excel libname engine does. Take a look at http://books.google.ca/books?id=QOI_xTgWwgsC&pg=PA35&lpg=PA35&dq=import+excel+libname+engine+format+sas&source=bl&ots=9pPB5QpKBB&sig=G35nn4hp99h9J8B7ittYhruMv3I&hl=en&sa=X&ei=4_VvUrnKNIeh8gG1jYC4Cg&ved=0CFIQ6AEwBTgU#v=onepage&q=import%20excel%20libname%20engine%20format%20sas&f=false

and, specifically, search that file for DBTYPE

Re: Proc import

Tal — Tue, 29 Oct 2013 21:40:20 GMT

thanks Arthur

Re: Proc import

Peter_C — Mon, 04 Nov 2013 10:07:54 GMT

Art

as you haven't mentioned it, I might be bringing up an idea that is as new to you as it was to me (last week!)

Use the SASTRACE option before PROC IMPORT from excel file types, to see the underlying SAS SQL code with options passed into excel (a bit like getting the code for CSV import).

The standard sastrace option I used :

options sastrace=',,,d' sastraceloc=saslog nostsuffix;

The examples in the documentation of SASTRACE reveal an even greater degree of detail might be available.

regards

Peter

Re: Proc import

art297 — Mon, 04 Nov 2013 15:47:35 GMT

: No, that was new to me as well! Yes, some other option settings would be needed to see exactly what they (SAS) did, and how, but it appears that the import is simply using the excel engine in a libname statement and then running proc sql and setting everything needed using various dbdsopts settings. Yes, definitely similar to getting the code for CSV imports.

Much appreciated,

Art