topic Re: Print Data from an excel file in SAS Studio. in SAS Studio

Print Data from an excel file in SAS Studio.

aabbccwyt — Thu, 26 Sep 2019 18:38:04 GMT

Hi. How can I print first 50 rows of a worksheet in an excel sheet in SAS Studio? And for the data selected from the worksheet, how to print the Date as mm/dd/yy, and Gross with dollar signs and commas. I'm using the University Edition. Thank you so much.

Re: Print Data from an excel file in SAS Studio.

Kurt_Bremser — Thu, 26 Sep 2019 18:46:07 GMT

Import Excel data into a SAS dataset and use obs=50 as a dataset option when using proc print to print the dataset.

Assign proper formats to the variables in question. The documentation of formats is found here.

Re: Print Data from an excel file in SAS Studio.

Reeza — Thu, 26 Sep 2019 18:48:32 GMT

1. First import the data, PROC IMPORT or LIBNAME
2. PROC PRINT with the OBS= option.
3. FORMAT statements to control display.

proc print data=sashelp.class (obs=10);
format weight 8.1;
format age dollar12.;
run;

Re: Print Data from an excel file in SAS Studio.

aabbccwyt — Thu, 26 Sep 2019 20:38:59 GMT

Here is what I have:

proc print data=work.Movie_2019 (obs=15);
format MMDDYY10. ;
format DOLLARw.d ;
format COMMAw.d;
run;

It gives the error message:

ERROR 22-322: Syntax error, expecting one of the following: a name, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 76-322: Syntax error, statement will be ignored.

The requirements are to have the Date printing as mmddyy, so I use the format MMDD10.; Gross with dollar signs and commas, so I use the formats DOLLARw.d and COMMAw.d

Re: Print Data from an excel file in SAS Studio.

Kurt_Bremser — Thu, 26 Sep 2019 20:43:09 GMT

To assign a format to a variable, you need to have both the variable name and the format in the format statement.

See the documentation for this.

Re: Print Data from an excel file in SAS Studio.

aabbccwyt — Thu, 26 Sep 2019 21:20:32 GMT

Is this correct?

proc print data=work.Movie_2019 (obs=50);
format ReleaseData MMDDYY10.;
run;

Or should there be space or _ between Release and Data

Re: Print Data from an excel file in SAS Studio.

Reeza — Thu, 26 Sep 2019 21:28:42 GMT

Do you have two variables or one?
You need to list each variable separated by a space if you want multiple variables to have one format.

Re: Print Data from an excel file in SAS Studio.

aabbccwyt — Thu, 26 Sep 2019 21:38:55 GMT

I have only one variable, however, the title of the variable in the excel sheet is "Release Date". I'm struggling with the format. Should it be Release Data, Release_Data or ReleaseData? Or should I do something else? Thank you.

Re: Print Data from an excel file in SAS Studio.

Kurt_Bremser — Thu, 26 Sep 2019 22:05:53 GMT

@aabbccwyt wrote:

I have only one variable, however, the title of the variable in the excel sheet is "Release Date". I'm struggling with the format. Should it be Release Data, Release_Data or ReleaseData? Or should I do something else? Thank you.

Then you need to use a so-called "name literal", which allows to use almost any crap as a variable name:

format "Release Date"n mmddyy10.;

Re: Print Data from an excel file in SAS Studio.

aabbccwyt — Thu, 26 Sep 2019 22:31:09 GMT

I think I have everything right, however, the format is still wrong. Can you please check my code to see if there is anything wrong? Thank you.

proc import datafile="/home/Movies_Database_2018.xlsx"
dbms=xlsx out=Movies_2018 replace;
run;

proc print data=Movies_2018 (obs=50);
format Gross dollar2.;
format Gross comma10.;
format "Release Data"n mmddyy10.;
run;

The data is just fine, just the format is not correct at all. No error, just a warning says " Variable 'Release Data'n not found in data set MOVIES_2018.

Re: Print Data from an excel file in SAS Studio.

Kurt_Bremser — Fri, 27 Sep 2019 06:21:44 GMT

View the dataset to see the variable name with which it was imported, or run proc contents on it and look at the results.

Re: Print Data from an excel file in SAS Studio.

Reeza — Fri, 27 Sep 2019 15:34:05 GMT

Don't guess your names and variable types, check it explicitly. Especially from Excel files because then SAS guesses at types, whereas with a text file you can control the type.

Run a proc contents on your data set that you imported and it will list the variable names and labels.

proc contents data=movies_2018;
run;

If you're still having issues after this, please post the output from PROC CONTENTS to the forum.

@aabbccwyt wrote:

I think I have everything right, however, the format is still wrong. Can you please check my code to see if there is anything wrong? Thank you.

proc import datafile="/home/Movies_Database_2018.xlsx"
dbms=xlsx out=Movies_2018 replace;
run;

proc print data=Movies_2018 (obs=50);
format Gross dollar2.;
format Gross comma10.;
format "Release Data"n mmddyy10.;
run;

The data is just fine, just the format is not correct at all. No error, just a warning says " Variable 'Release Data'n not found in data set MOVIES_2018.

Re: Print Data from an excel file in SAS Studio.

Tom — Fri, 27 Sep 2019 16:49:46 GMT

The data is just fine, just the format is not correct at all. No error, just a warning says " Variable 'Release Data'n not found in data set MOVIES_2018.

Why did you reference a variable that doesn't exist? Could be just a typo. I doubt that the name is 'Release Data'n for a value that has a date in it.

Make sure not to use the LABEL of the variable instead of the NAME of the variable. The setting of the VALIDVARNAME option will determine whether SAS will create a variable named 'Release Date'n or Release_Date, but in either case the label will reflect the column header in the Excel file.

Also why are you using two different FORMAT statements for the variable GROSS? Which way would you like it to display the values? With or without DOLLAR signs?