BookmarkSubscribeRSS Feed
acordes
Rhodochrosite | Level 12

I do not succeed in defining a numeric format in a "case when" construct in fedsql. 

 

It gives en error in the proc summary code:

 

ERROR: Variable RENEWED in list does not match type prescribed for this list.

proc cas;
source MPG_toyota;
create table mkt.testa {options  replication=0 REPLACE=true } as select 
a.*, case when b.numbasti='' then put(0, 3.) else put(1, 3.) end as renewed , b.CODOPERA_FOR, b.NUMBASTI_FOR   
from public.denom a left join public.nom b
on a.numbasti=b.numbasti;;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;


proc summary data=mkt.testa print ;
format _FECULVTO year. ;
class from_brand _FECULVTO ;
var renewed;
output out=test sum= mean= / autoname;
run;

 

11 REPLIES 11
tarheel13
Rhodochrosite | Level 12

proc summary requires a numeric variable. open up mtk.testa dataset and verify whether renewed is char or numeric. I believe the error is how you're creating renewed in proc cas.

ballardw
Super User

@tarheel13 wrote:

proc summary requires a numeric variable. open up mtk.testa dataset and verify whether renewed is char or numeric. I believe the error is how you're creating renewed in proc cas.


For VAR variables. Class, By and ID variables can be character or numeric.

So the variable Renewed in this code is not numeric.

 

 

acordes
Rhodochrosite | Level 12

I haven't expressed myself correctly. 

I know that it's not numeric therefore giving an error. 

 

But HOW do I create a valid numeric variable in fedsql so that it executes correctly?

If I do the same in proc sql then it runs without problems. 

 

This works:

options casdatalimit=all;
proc sql;
create table testa  as select 
a.*, case when b.numbasti='' then 0 else 1 end as renewed , b.CODOPERA_FOR, b.NUMBASTI_FOR   
from public.denom a left join public.nom b
on a.numbasti=b.numbasti;
quit;

proc summary data=testa print ;
format _FECULVTO year. ;
class from_brand _FECULVTO ;
var renewed;
output out=test sum= mean= / autoname;
run;

 

Kurt_Bremser
Super User

Omit the PUT function, which always results in character:

create table mkt.testa {options  replication=0 REPLACE=true } as
  select 
    a.*,
    case when b.numbasti=''
      then 0
      else 1
    end as renewed,
    b.CODOPERA_FOR,
    b.NUMBASTI_FOR   
  from public.denom a left join public.nom b
  on a.numbasti=b.numbasti
;
acordes
Rhodochrosite | Level 12

I had tried before, it still gives error. 

 

1 %studio_hide_wrapper;
82 proc cas;
83 source MPG_toyota;
84 create table mkt.testa {options replication=0 REPLACE=true } as
85 select
86 a.*,
87 case when b.numbasti=''
88 then 0
89 else 1
90 end as renewed,
91 b.CODOPERA_FOR,
92 b.NUMBASTI_FOR
93 from public.denom a left join public.nom b
94 on a.numbasti=b.numbasti
95 ;
96 endsource;
97 fedSQL.execDirect / query=MPG_toyota;
98 quit;
NOTE: Active Session now MYSESSION.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a TIMESTAMP column has been requested, but is not supported by the CASDAL driver. A DOUBLE
PRECISION column will be created instead. A DATETIME format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a TIMESTAMP column has been requested, but is not supported by the CASDAL driver. A DOUBLE
PRECISION column will be created instead. A DATETIME format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: CASDAL driver. Creation of a DATE column has been requested, but is not supported by the CASDAL driver. A DOUBLE PRECISION
column will be created instead. A DATE format will be associated with the column.
NOTE: Table TESTA was created in caslib MKT with 36688 rows returned.
NOTE: PROCEDURE CAS used (Total process time):
real time 0.75 seconds
cpu time 0.02 seconds

99
100 proc summary data=mkt.testa print ;
101 format _FECULVTO year. ;
102 class from_brand _FECULVTO ;
103 var renewed;
104 output out=test sum= mean= / autoname;
105 run;
NOTE: The CAS aggregation.aggregate action will be used to perform the initial summarization.
ERROR: The analytic variable's data type is not supported.

Kurt_Bremser
Super User

Since the SQL code complains about a DATE, I think that _FECULVTO is the culprit here.

See if this works:

proc cas;
source MPG_toyota;
create table mkt.testa {options  replication=0 REPLACE=true } as
  select 
    a.from_brand,
    year(a._FECULVTO) as FECULVTO,
    case when b.numbasti=''
      then 0
      else 1
    end as renewed,
    b.CODOPERA_FOR,
    b.NUMBASTI_FOR   
  from public.denom a left join public.nom b
  on a.numbasti=b.numbasti
;
endsource;
fedSQL.execDirect / query=MPG_toyota;
quit;


proc summary data=mkt.testa print;
class from_brand FECULVTO;
var renewed;
output out=test sum= mean= / autoname;
run;
acordes
Rhodochrosite | Level 12

Thanks Kurt.

But that doesn't fix it either. 

Tom
Super User Tom
Super User

What if you add a decimal point? 0.0 and 1.0 instead of 0 and 1?

What if you list explicitly which variables you want instead of using SELECT *?

acordes
Rhodochrosite | Level 12

Thanks Tom,

I've tried selecting only a few variables and 1.0 and 0.0 instead of my then else values. 

The same error. 

I write some extra data step or select case when explicitly as character "1" and "0". This works and I use it in the class statement of the proc summary. 

It's a strange behavior of fedsql I'd say...

Tom
Super User Tom
Super User

In PROC SQL if you try to create two variables with the same name only the first one survives.

Try it.

proc sql;
create table test as
  select name , age as name
  from sashelp.class
;
quit;

I assume FEDSQL will exhibit the same behavior.

 

Make sure that the name renewed has not already been used by another variable that appears before the CASE expression in your SELECT statement.

Tom
Super User Tom
Super User

Are you sure that you have actually replaced the dataset?

What happens if you use a different dataset name, one that does not already exist.

Do you get a different error message from the PROC SUMMARY step?

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 11 replies
  • 2983 views
  • 1 like
  • 5 in conversation