BookmarkSubscribeRSS Feed
TimMandell
Obsidian | Level 7

I would like to code out an import I was able to perform using the import Wizard in SAS Enterprise Guide 9.4.

It allowed for me to:

import a CSV file

set the first observation row

get the names for use as column names

Spefcify where the next observation will start

define how many observations to get there after

 

my data is really fun and looks something like the following

 

 

01/01/2016,

 

Start Stats, type, location, value, quanity value,

                 ,-----,-----------,--------,-------------,

                 ,new, Central, 100, 500,

 

End Stats, type, location, value, quantiy value,

                 ,-----,-----------,--------,-------------,

                 ,used, Central, 50, 10,

 

the end result is to:

populate the column names using each Stats line

ommit the dash lines

populate the data portion into table values.

Later I will merge all the items into one table on one line item for each file.

 

I have several tables and the column names can change so getting the column names is very important.

 

I'm unable to get the Wiard to provide it's import method to understand what's going on there to springboard my project.

16 REPLIES 16
ChrisHemedinger
Community Manager

If you're looking for the Import Data task to give you reusable code, check the following options.

 

  • On the first screen, click Performance.  In the Performance Options window, check the "Bypass the data cleansing process" box.
  • On the last screen (Advanced Options), check the "Generalize import step to run outside SAS Enterprise Guide" box.

The resulting code should be something that will run as-is in its own program, as long as the CSV file that you reference is in a path that the SAS session can get to.

 

Chris

 

 

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
TimMandell
Obsidian | Level 7

yes it does and no it doesn't....

 

The resulting code does't show the method used to get the column names.

 

It only shows hard coded outcomes of evaluation for column names.

 

This is great but doesn't lend it's self well to a dynamic process.

 

This is still a step forward from not using these steps.

 

Previouse the file was only represented by datalines4

Reeza
Super User

Well...no, the column names are part of the code. 

 

If you want SAS to guess at the column names you can use PROC IMPORT instead, but it can guess incorrectly, which sets up issues down the road. You may also run into issues with where EG is located and your files are located, ie the server not seeing your local directory issues. 

TimMandell
Obsidian | Level 7

Yes...

 

I would like to use proc import method and have SAS guess at the names.

And I've tried without success .. my column names contiue to populate as VARx and I'm not able to pinpoint where the data starts to omit the dash line.

 

I was hoping to mimic the import wizard and modified as needed to produce the final outcome.

Reeza
Super User

Have you specified GETNAMES=Yes?

 

The options are here:

http://support.sas.com/documentation/cdl/en/acpcref/67382/HTML/default/viewer.htm#p07k868np8cd18n1te...

 

I think you're also interested in NAMEROW and DATAROW options as mentioned in the GUI.

 

 

TimMandell
Obsidian | Level 7

yes so using the code like

proc

import datafile='C:test.CSV' dbms=csv out=work.test replace;

datarow=4;

getnames=yes;

GUESSINGROWS=500;

 run;

 

the data as provided before demonstrates VARx for all column names.

 

My first guess is SAS is protecting it's self from values that have spaces

 

 

 

 

 

 

Reeza
Super User
I think that SAS knows how to handle names with spaces.

My guess would be that its assuming a namerow option that doesn't match up with your data. So when you say datarow=4 is the namerow=1, 3 or 4? At any rate, try explicitly specifying namerow as well when you do specify datarow.

ChrisHemedinger
Community Manager

SAS can handle var names with spaces if you specify OPTIONS VALIDVARNAME=ANY.  Then you can reference the variable with a special literal notation in code, like this:

 

  length 'my variable'n 8;

 

But not sure that's what is getting in your way here.  If you can post a sample data file with a few records (beyond what you've already supplied), I'm sure that someone will help with an approach.  I suspect the answer is not PROC IMPORT but a DATA step that reads and "fixes" the data lines on the way in.  Or two passes: one to clean the file and produce a more typical CSV, and the second to run the PROC IMPORT that will determine column attributes.

 

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
TimMandell
Obsidian | Level 7
Sure... here's a file layout I typically interact with... each of the heders or topic lines can change as well as the error summary at the bottom of the file... Setting OPTIONS VALIDVARNAME=ANY; did not provide the column names... only VRx continue to populate the only difference here is the file extesion should be test.CSV but SAS web does not allow it as a valid file extension
ChrisHemedinger
Community Manager

Wow!

 

That is very possible to read using a DATA step, but it will require a completely custom approach.  It's a far stretch to call that a CSV file -- it does not comply with any variation of CSV I've encountered.  I don't see how the Import tasks can help you at all.

 

Here's what I suggest - create another top in the Base Programming board with a title like "Help reading in text file with custom layout".  Post the example layout and ask for help with the technique.  

 

Who knows? Someone might solve it for you.  More likely people will point you to examples if using the INPUT statement and @ to control input position and other necessary tricks.

 

Chris

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.
Reeza
Super User

You're attempting to fix a broken process with code.

 

That output comes from a reporting system that probably stores the data in a better format.

 

Go back to the source and ask for datasets not text files. 

Fix the process instead of work around it, yes, it's more work.

And yes, its not always possible 😞

TimMandell
Obsidian | Level 7

Unfortunatly I'm working in an established proccess without wiggle room to request changes The method I've come up with by combining stuff from other post is something like grab all directory information from the home folder where file is CSV-- 

 

%let dirname = C:\Test;
filename DIRLIST pipe "dir /B &dirname\*.csv";

data dirlist;
    length fname $256;
    infile dirlist length=reclen;
    input fname $varying256. reclen;
run;

/*Loop through each file grabbing only specific data lines-- 
  This does not allow for files where column ccounts may be more or 
  less then what I've specified so I've over requested columns. */
data rep_date;
length myfilename $100;
set dirlist;
filepath = "&dirname\"||fname;
 /* change firstobs to assure that the column header is first observation in new table */
infile dummy filevar = filepath dlm='2C0D'x dsd missover 
   lrecl=10000 firstobs=3 obs=3 end=done missover;
do while(not done);
    myfilename = filepath;
    INPUT F1 : $CHAR96. 
        F2 : $CHAR33.   F3 : $CHAR18.   
        F4 : $CHAR55.   F5 : $CHAR11.   
        F6 : $CHAR11.   F7 : $CHAR11. 
        F8 : $CHAR11. F9 : $CHAR11.   
        F10 : $CHAR11.  F11 : $CHAR11. 
        F12 : $CHAR11.   F13 : $CHAR11.;
    output;
end;
run;

/*Assemble all the pieces- but this method does not create 
 variables for the column names so it does not allow for varrying column names */
proc sql;
create table final as (
select s.batch_id , s.extract_date , s.submit_date , 
    s.system_name , s.record_count , s.encounter_count , s.svc_from_date , 
    s.svc_to_date , a.F1 as rep_date , 
    case when c.f2 like '%SUB%' then c1.f2 end as enc_SUBMIT , 
    case when c.f3 like '%PROC%' then c1.f3 end as enc_PROCES , 
    case when c.f4 like '%CLOSE%' then c1.f4 end as enc_CLOSED , 
    case when c.f5 like '%DUP%' then c1.f5 end as enc_DUPLICATE , 
    case when c.f6 like '%ACCEPT%' then c1.f6 end as enc_ACCEPTED , 
    case when c.f7 like '%INFOR%' then c1.f7 end as enc_INFORMATION , 
    case when c.f8 like '%JECTED%' then c1.f8 end as enc_REJECTED , 
    case when c.f9 like '%PART RE%' then c1.f9 end as enc_PART_REJECT , 
    case when d.f2 like '%SUB%' then d1.f2 end as diag_SUBMIT , 
    case when d.f3 like '%PROC%' then d1.f3 end as diag_PROCES , 
    case when d.f4 like '%CLOSE%' then d1.f4 end as diag_CLOSED , 
    case when d.f5 like '%DUP%' then d1.f5 end as diag_DUPLICATE , 
    case when d.f6 like '%ACCEPT%' then d1.f6 end as diag_ACCEPTED , 
    case when D.f7 like '%INFOR%' then D1.f7 end as DIAG_INFORMATION , 
    case when D.f8 like '%JECTED%' then D1.f8 end as DIAG_REJECTED , 
    case when D.f9 like '%PART RE%' then D1.f9 end as DIAG_PART_REJECT 
from rep_date a inner join batch b on a.fname =b.fname INNER JOIN submitted S ON b.F2 =S.BATCH_ID 
inner join( select * from enc where f1 is not null) c on a.fname =c.fname 
inner join( select * from enc where f1 is null and f2 not like '%-%') c1 on a.fname =c1.fname 
inner join( select * from diag where f1 is not null) d on a.fname =d.fname 
inner join( select * from diag where f1 is null and f2 not like '%-%') d1 on a.fname =d1.fname 
where b.f2 not like'%BATCH%' and b.f2 not like'%-%' );
quit;
TimMandell
Obsidian | Level 7
sorry the last port was a holy mess. The compay I work for only allows IE8 and SAS does not support it.
ChrisHemedinger
Community Manager
I pasted into EG and formatted, then applied to your message. Hopefully the code is the same as what you supplied 🙂
It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 16 replies
  • 3133 views
  • 1 like
  • 3 in conversation