topic Re: How to import a delimited txt file in SAS Enterprise Guide? in SAS Programming

How to import a delimited txt file in SAS Enterprise Guide?

Nancy05 — Tue, 28 Jun 2016 22:45:50 GMT

Hi,

I have repared a delimited txt file in my C drive. I try to imput the txt in SAS Enerprise Guide. Here is my SAS code:

data work.sales2;
    length First_Name $ 12 Last_Name $18 Gender $1 Job_Title $ 25 Country $ 2;
	infile "C:\Users\999152\temp\sales2.txt" dlm=',';
	input Employee_ID First_Name :$12.
	Last_Name :$18. Gender :$1.  Salary Job_Title :$25. Country :$2.
	Birth_Date :date. Hire_Date :mmddyy.;
	format Salary dollar12. Birth_Date Hire_Date monyy7.;
run; 

proc print data= work.sales2;
run;

But I got error message:

NOTE: Variable First_anem is uninitialized.
ERROR: Physical file does not exist, C:\Users\999152\temp\sales2.txt.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.SALES2 may be incomplete.  When this step was stopped there were 0 observations and 10 variables.
WARNING: Data set WORK.SALES2 was not replaced because this step was stopped.

How do I input delimited txt file into SAS and convert it into SAS data set?

Thanks!

Nancy

Re: How to import a delimited txt file in SAS Enterprise Guide?

Reeza — Tue, 28 Jun 2016 22:51:54 GMT

1. Spelling mistake somewhere in your code - First_anem (but not visible so is the code here exactly what you used?)

2. Make sure the path is correct AND that your SAS installation has access. For example if you are using EG on a server it may not have access to your desktop. In this case you need to use the Wizard or Task to import the data.

3. Are you sure you need the colon modifiers on your input statement?

Re: How to import a delimited txt file in SAS Enterprise Guide?

Tom — Tue, 28 Jun 2016 23:20:09 GMT

Your main problem is probably that SAS is running somewhere other than your PC and so it cannot see files on your C: drive. Either put the file somewhere that can be seen by the SAS process or use some EG tool/process to upload the file.

Also your program is a little over complicated. I find it much easier to define the variables and then input them. You should use the DSD option so that empty values (adjacent delimiters) will be properly treated. You don't need colon modifiers. In fact don't include informats in the INPUT statement at all. You dont need them for numbers or character strings and for dates and times use and INFORMAT statement (or INFORMAT= option on ATTRIB statement) to let SAS know how to translate the raw data stream.

data sales2;
  length Employee_ID 8 First_Name $12 Last_Name $18 Gender $1
         Salary 8 Job_Title $25 Country $2 Birth_Date 8 Hire_Date 8
  ;
  infile "C:\Users\999152\temp\sales2.txt" dsd truncover ;
  input Employee_ID -- Hire_Date ;
  informat Salary comma. Birth_Date date. Hire_Date mmddyy.;
  format Salary dollar12. Birth_Date Hire_Date monyy7.;
run;

You might also need to add FIRSTOBS=2 to the INPUT statement if the file has a header row.

Re: How to import a delimited txt file in SAS Enterprise Guide?

Nancy05 — Tue, 28 Jun 2016 23:22:50 GMT

Hi Reeze,

1. Yes. I have corrected my variable name. First_Name is correct.

2. Yes. The file path is correct. I agree with that EG on a server it may not have access to my desktop.

How do I use Task/Wizard to import the data? Task-> Data -> ????

I could see anything from Task menu I could import the file. I have tried Task -> Data-> Upload Data Files to Server. It can only upload SAS file. But I need convert text file to SAS file first.

3. Yes. I don't have to use the colon modiferies on my input statement. So I have modified the SAS code and still get the error messag.

data work.sales2;
    length First_Name $ 12 Last_Name $18 Gender $1 Job_Title $ 25 Country $ 2;
	infile "C:\Users\999152\temp\Sales2.txt" dlm=',';
	input Employee_ID First_Name $
	Last_Name $ Gender $  Salary  Job_Title Country $
	Birth_Date :date. Hire_Date :mmddyy.;
	format Salary dollar12. Birth_Date Hire_Date monyy7.;
run; 

proc print data= work.sales2;
run;

ERROR: Physical file does not exist, C:\Users\999152\temp\Sales2.txt.
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.SALES2 may be incomplete.  When this step was stopped there were 0 observations and 9 variables.
WARNING: Data set WORK.SALES2 was not replaced because this step was stopped.

Thanks!

Nancy

Re: How to import a delimited txt file in SAS Enterprise Guide?

Tom — Tue, 28 Jun 2016 23:27:50 GMT

Check out this blog post on tools for copy files. If you have an older version of EG there is a link to earlier post that might work for you.

http://blogs.sas.com/content/sasdummy/2015/03/12/enterprise-guide-71-tasks/

Re: How to import a delimited txt file in SAS Enterprise Guide?

Nancy05 — Tue, 28 Jun 2016 23:47:37 GMT

Hi Tom,

Thanks for you blog post. The "Copy files" screen looks good, it is what I need. The problem is that I could not see such usful tool/window in my EG. I use EG 7.12.

When I use Task-> Data -> Upload Data Files to Server, I got this screen>

How do I access the screen showed in your post log?

http://blogs.sas.com/content/sasdummy/2015/03/12/enterprise-guide-71-tasks/

Thanks!

Nancy

Re: How to import a delimited txt file in SAS Enterprise Guide?

Reeza — Wed, 29 Jun 2016 00:16:14 GMT

These are custom tasks. You need to download and install them, and you probably need admin privileges to do so.

Check the File menu for an option to import data.

Re: How to import a delimited txt file in SAS Enterprise Guide?

Nancy05 — Wed, 29 Jun 2016 01:52:47 GMT

Hi Reeza,

File-> import Data works.

Thank you so much!

Nancy

Re: How to import a delimited txt file in SAS Enterprise Guide?

Nancy05 — Wed, 29 Jun 2016 21:58:14 GMT

Hi Tom,

Thank you so much for giving the hint of using dsd. I have put the file in shared file drive and make it working. I have replaced the driver name and path name in the code below.

data work.sales3;
    length Employee_ID 8 First_Name $ 12 Last_Name $18 Gender $1 
	Salary 8 Job_Title $ 25 Country $ 2; 
	infile "\\<driver Name>\<Path Name>\Sales3.txt" dsd truncover;
	input Employee_ID First_Name Last_Name Gender Salary Job_Title Country Birth_Date :date.
	Hire_Date :mmddyy.;
	informat Salary comma. Birth_Date date. *Hire_Date :mmddyy.;
	format Salary dollar12. Birth_Date monyy7. Hire_Date DATE9.;
	label Job_Title= 'Sales Title' Birth_Date='Birth Date' Hire_Date = 'Hire Date';
run; 

Title "Import raw data text file test";
proc print data= work.sales3 label;;
run;

Thank you again!

Nancy