DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 61
Accepted Solution

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

[ Edited ]

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


Accepted Solutions
Solution
‎06-29-2016 05:59 PM
Contributor
Posts: 61

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

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

View solution in original post


All Replies
Super User
Posts: 17,823

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

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?

Contributor
Posts: 61

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

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

Super User
Super User
Posts: 6,500

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

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/

 

Contributor
Posts: 61

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

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>

 

UploadDataFilesToServer.GIF

 

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

 

Super User
Posts: 17,823

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

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. 

Contributor
Posts: 61

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

Hi Reeza,

 

File-> import Data works.

 

Thank you so much!

 

Nancy

Super User
Super User
Posts: 6,500

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

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.

Solution
‎06-29-2016 05:59 PM
Contributor
Posts: 61

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

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 364 views
  • 2 likes
  • 3 in conversation