BookmarkSubscribeRSS Feed
jwhite
Quartz | Level 8

We're trying to incoroprate Data Management Studio into our process by cleaning up data before doing our transformations and such in DI Studio.

 

Source data comes from Excel, and we use a user written code node in DI Studio to write a proc import statement to turn the excel spreadsheets into sas datasets. The output datasets look like attached image, but we want them to look as follows:

 

ID        Name        Date

1          John         Fall 2013

2         Juan          Fall 2013

3         Kara          Fall 2013

 

So, my questions are...

 

1) Is there a way to do this in the proc import statement? Create a new column and provide the value for it?

2) If not, can this be done in Dataflux - Data Management Studio?

 

The idea is that we convert the spreadsheets into sas datasets, Dataflux cleans the data, then DI Studio continues processing the clean datasets.

 

I can do the above in DI Studio (and probably the data standardization with lookup tables), but again we want to do cleaning and standardizing in Dataflux if possible.

 

Are we going about this the wrong way?

 

Thanks!

 

 

 


sourcetb.PNG
2 REPLIES 2
SASKiwi
PROC Star

Please post your PROC IMPORT code. You should be able to get the desired output by using either the RANGE, STARTCOL, or STARTROW statements in your PROC IMPORT code. Check the following link to confirm what options are available for your type of Excel workbook:

 

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

 

jwhite
Quartz | Level 8

Ok, the code is below. If I could output all the sheets into one dataset and capture that semester/year data from one cell to populate into a new column....that would be a big help. I'm currently outputting each sheet as it's own dataset.

 

libname cbsch 'S:\path\;

%macro pim(sheet);

proc import out=sch

    datafile = 'S:\path\file.xlsx'
	 out = cbsch.CB_SCH_&sheet 
    dbms = Excel replace;
    sheet = "&sheet";
    mixed = yes;
    getnames = yes;

run;


%mend pim;

%pim(sffall2013);
%pim(sfspring2014);
%pim(sfsummer2014);
%pim(sfFY14);

 

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!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 953 views
  • 0 likes
  • 2 in conversation