Print Data from an excel file in SAS Studio.

[aabbccwyt]

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.

[Kurt_Bremser]

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.

[Reeza]

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;

[aabbccwyt]

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

[Kurt_Bremser]

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.

[aabbccwyt]

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

[Reeza]

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.

[aabbccwyt]

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.

[Kurt_Bremser]

---

@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.;

[aabbccwyt]

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. 

[Kurt_Bremser]

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

[Reeza]

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. 

---

[Tom]

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?