topic Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1 in SAS Enterprise Guide

Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Luciferene — Tue, 18 Sep 2018 16:50:59 GMT

data Honda;
input CLASSES $ RATIO N;
cards;
C 0.75 500
B 1.00 300
A 1.25 400
;

data Porche;
input  CLASSES $ RATIO N;
cards;
C 0.90 250
B 1.00 100
A 1.25 50
;

PROC SQL;
	CREATE TABLE WORK.COMPARISON ('Car Name'n char, 'Total # of Cars'n num, 'Weighted Ratio'n num);
	SELECT 'Honda' AS 'Car Name'n, sum(N) AS 'Total # of Cars'n, SUM(RATIO * N)/SUM(N) AS 'Weighted Ratio'n FROM WORK.Honda
	UNION
	SELECT 'Porche' AS 'Car Name'n, sum(N) AS 'Total # of Cars'n, SUM(RATIO * N)/SUM(N) AS 'Weighted Ratio'n FROM WORK.Porche;
QUIT;

Hello,

Basically the problem is self explanatory when you run the above example program I made. The program creates both SAS Report and Dataset, however the report is the only one that contains any data.

Dataset basically comes out blank with only the column names.

Thanks in advance for your help!

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Tom — Tue, 18 Sep 2018 16:55:52 GMT

It is just doing what you asked it to do. You have two statements. One to create an empty dataset and one to select data (from other dataset).

If you want to create a table then create the table in one step.

create table x as select a,b,c from y;

If you want to see what is in the data then print that.

Once it is in a dataset then you could just print it using normal SAS procedures instead of messing with SQL.

proc print data=x;
run;

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Tom — Tue, 18 Sep 2018 16:58:50 GMT

PROC SQL;
CREATE TABLE WORK.COMPARISON as
SELECT 'Honda' AS 'Car Name'n
    , sum(N) AS 'Total # of Cars'n
    , SUM(RATIO * N)/SUM(N) AS 'Weighted Ratio'n 
FROM WORK.Honda

UNION

SELECT 'Porche' AS 'Car Name'n
     , sum(N) AS 'Total # of Cars'n
     , SUM(RATIO * N)/SUM(N) AS 'Weighted Ratio'n 
FROM WORK.Porche
;
QUIT;

proc print data=WORK.COMPARISON;
run;

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

VDD — Tue, 18 Sep 2018 17:24:04 GMT

Can you have names with spaces like: Car Name

or does the name need to be like this: Car_Name

@novinosrinthanks for reminding be about the validvarname = any;

Is the default normally set to validvarname = no?

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

novinosrin — Tue, 18 Sep 2018 17:06:10 GMT

@VDD yes you can with

options validvarname=any;
PROC SQL;
	CREATE TABLE WORK.COMPARISON as
	SELECT 'Honda' AS 'Car Name'n, sum(N) AS 'Total # of Cars'n, SUM(RATIO * N)/SUM(N) AS 'Weighted Ratio'n FROM WORK.Honda
	UNION
	SELECT 'Porche' AS 'Car Name'n, sum(N) AS 'Total # of Cars'n, SUM(RATIO * N)/SUM(N) AS 'Weighted Ratio'n FROM WORK.Porche;
QUIT;

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

novinosrin — Tue, 18 Sep 2018 17:54:53 GMT

default is set to v7

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Luciferene — Tue, 18 Sep 2018 18:51:29 GMT

Thanks @Tom, I see where I went wrong. In my lack of programming knowledge, I thought it was creating an empty dataset and then populating it with values selected from another table.

Is it not possible to include column definition when creating a new table and populating it this way? I'm not sure if it can be done through column definition but I wanted to eventually changed weighted ratio to xxx% format.

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Tom — Tue, 18 Sep 2018 19:02:15 GMT

If you want to attach a format to a variable you can use the FORMAT= keyword. You can also force a LENGTH. Or attach an INFORMAT or a LABEL to the variable.

select
  'SAMPLE' as name length=20 
 ,a/b as ratio format=percent6.2 
from have
;

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Luciferene — Tue, 18 Sep 2018 19:04:56 GMT

Oh is it? I never got syntax error even though I didn't set it to any.

On personal note, I should probably add that line to avoid issues in future.

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Tom — Tue, 18 Sep 2018 19:10:57 GMT

Programming will be much easier if you just use valid names for your variables and datasets. You can use the LABEL option to place user friendly text for reports.

proc sql;
CREATE TABLE COMPARISON as
SELECT 'Honda' AS  Car label='Car Name'
    , sum(N) AS N label='Total # of Cars'
    , SUM(RATIO * N)/SUM(N) AS Ratio label='Weighted Ratio'
FROM  Honda
;
quit;

proc print data=comparison label;
run;

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Luciferene — Tue, 18 Sep 2018 19:53:26 GMT

Thank you, that's a great suggestion.

I made the edits and now I have some follow up questions.

data Honda;
input CLASSES $ RATIO N;
cards;
C 0.75 500
B 1.00 300
A 1.25 400
;

data Porche;
input  CLASSES $ RATIO N;
cards;
C 0.90 250
B 1.00 100
A 1.25 50
;



PROC SQL;
	CREATE TABLE WORK.COMPARISON as
	SELECT 'Zonda' AS Car label='Car Name', sum(N) AS n label='Total # of Cars', SUM(RATIO * N)/SUM(N) AS Ratio Label='Weighted Ratio' format=percent6.2 FROM WORK.Honda
	UNION
	SELECT 'Porche' AS Car, sum(N) AS n, SUM(RATIO * N)/SUM(N) AS Ratio FROM WORK.Porche;
QUIT;

PROC SQL;
	CREATE TABLE WORK.FINAL AS
	SELECT * FROM WORK.COMPARISON
	UNION
	SELECT 'TOTAL' AS CAR,. AS n, SUM(n) AS RATIO FROM WORK.COMPARISON;
QUIT;

PROC PRINT DATA=WORK.FINAL LABEL;
RUN;

SAS seems to sort all the rows added by the first column regardless of the order in which they got put in, how would I go about rearranging the rows in the way I want them to be?

For example if I had Honda, GM, Toyota, BMW and I wanted them in that order in particular (just for the report) and not sort them alphabetically.

Also, is it possible for one column to have different formatting? So I would like the final report to show:

Car Name Total # of Cars Weighted Ratio

Zonda 1200 97%

Porche 400 98%

TOTAL 1600

Trying to mimic Excel report on SAS is proving to be big challenge in terms of formatting.

EDIT: Just adding on, would it be possible to remove the Obs column in the report?

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

ballardw — Tue, 18 Sep 2018 20:16:45 GMT

@Luciferene wrote:

Thank you, that's a great suggestion.

I made the edits and now I have some follow up questions.

EDIT: Just adding on, would it be possible to remove the Obs column in the report?

Option NOOBS (no obs=> no observation number)

PROC PRINT DATA=WORK.FINAL LABEL NOOBS;
RUN;

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Tom — Tue, 18 Sep 2018 22:05:17 GMT

SQL does not preserve order unless you tell it what variable to order on. You could add a variable to set the order.

Don't even attempt to put mixed values into the same variable. If you want to produce a report use a reporting procedure, look at PROC PRINT, PROC REPORT, PROC TABULATE, PROC FREQ.

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Luciferene — Tue, 18 Sep 2018 22:20:58 GMT

Thank you and also for the explanation. I just saw NOOBS on email preview and was confused.

Re: Program creates correct SAS Report but empty Dataset (with column names). Using SAS EG 7.1

Luciferene — Tue, 18 Sep 2018 22:21:50 GMT

Okay I'll try doing it with those methods. Thanks for the guidance!