BookmarkSubscribeRSS Feed
GrizzlyDee
Calcite | Level 5

You are faced with the task of saving an Excel xlsx file as a permanent data set after uploading to SAS Studio, then using proc contents to view the contents of the Excel file and proc freq to find the frequency in which a specific variable appears in the file. What does your basic program look like?

7 REPLIES 7
Cynthia_sas
SAS Super FREQ

Hi:
You cannot use the LIBNAME engine to run PROC CONTENTS directly on the XLSX file when you are using SAS Studio and SAS University Edition.

So, in brief, I would:
1) upload the XLSX file to a SAS Studio folder
2) run PROC IMPORT on the XLSX file using DBMS=XLSX to create a SAS dataset
3) run PROC CONTENTS on the SAS dataset from #2 (NOT the XLSX file from #1)
4) Run PROC FREQ on the datatset from #2 using the TABLE statement for my variable and specifying options like NOCUM, NOPERCENT, as needed.

 

Regarding #2, I would test my PROC IMPORT step by creating a WORK dataset first, then once I knew that the IMPORT was correct, I would issue a LIBNAME statement to a folder on the University Edition like:

libname myperm '/folders/myfolders/keepdata';

 

Then I would run PROC IMPORT using a 2-level name to save the SAS version of the XLSX file to this permanent library.


cynthia

GrizzlyDee
Calcite | Level 5
Give a basic template of what that statement would look like...

proc import data= .....
run;

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Reeza
Super User

@GrizzlyDee This reads like homework. Do you have a specific issue that you need help with instead of posting your assignment? 

 

SAS video tutorials are here:

http://support.sas.com/training/tutorial/

 

The first programming course is free and on the bottom right hand side of the page. 

 

Tons of SAS tutorials with code samples

http://www.ats.ucla.edu/stat/sas/

 

If you have a specific topic in mind try searching on Lexjansen.com

 

 

Cynthia_sas
SAS Super FREQ
Hi:
If you are using SAS Studio, there is a TASK that contains the basic template. The videos to which Reeza referred show you how to use TASKS (and SNIPPETS) to perform tasks. There is even a video on how to import an Excel file here, in the Forums on the top of the SAS Analytics U section:
https://communities.sas.com/t5/SAS-Analytics-U/bd-p/sas_analytics_u -- all of these resources are available and have been available to you.

With some research, you could have found the answers to these questions already available. Many of these answers have been available for several years, posted previously, here in the Forums.

cynthia
GrizzlyDee
Calcite | Level 5
Why use proc import to create the permanent data set if the xlsx file is
already uploaded to Sas Studio?

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Reeza
Super User

Uploading a dataset is merely storing it in a drive that SAS can access. 

Kind of like uploading a file to Dropbox. 

Cynthia_sas
SAS Super FREQ
SAS procedures like PROC CONTENTS and PROC PRINT will only work with XLSX files if/when you have a configuration that uses the LIBNAME engine for EXCEL which is what allows those procedures to work without being imported. As I indicated in my original response, the LIBNAME engine for Excel does not work in SAS Studio and the University Edition because the correct "helper" files are not installed.

cynthia

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 7 replies
  • 1665 views
  • 2 likes
  • 3 in conversation