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

[Luciferene]

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!

[Tom]

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;

[Luciferene]

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.

[Tom]

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
;

[Tom · Accepted solution]

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;

[VDD]

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?

[novinosrin]

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

[novinosrin]

default is set to v7

[Luciferene]

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.

[Tom]

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;

[Luciferene]

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?

[ballardw]

---

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

[Luciferene]

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

[Tom]

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.

[Luciferene]

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