Numerical value for a compressed function

[Nikhil28]

Hi,

I have compressed the following - "Box 0f 6 widgets" to give me "6" in a separate column.

Now I want to multiply this number with average price but the calcs give me an error "need a numercial value to use * multiplication".

Anyway to fix it?

Thanks

[novinosrin]

Can you post the sample of your data,code, log and the desired output plz

[ChrisNZ]

Instead of something like

  STR= compress( VAR,,'dk');

that you must have right now, use

  NUM= input( compress( VAR,,'dk'), 32.);

to create a numeric variable.

[novinosrin]

Sir @ChrisNZ nice point but i would have thought autoconversion would have only resulted in a note while OP says received an error?

[ChrisNZ]

@novinosrin No automatic conversion in SQL.

[Nikhil28]

1 The SAS System 08:54 Monday, October 29, 2018

1 ;*';*";*/;quit;run;
2 OPTIONS PAGENO=MIN;
3 OPTION DEBUG=DBMS_SELECT SQL_IP_TRACE=(NOTE, SOURCE);

4 PROC SQL NOEXEC;
5 SELECT (NUM= input( COMPRESS(t1.NON_WAREHOUSE_UOM, ,'dk') , 32.)) AS CALCULATION
6 FROM WORK.QUERY_FOR_PURCHASING_0000 t1;
ERROR: The following columns were not found in the contributing tables: NUM.
7 QUIT;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

8 %PUT SQL_IPTRACE_RESULT=&SYS_SQL_IP_ALL;
SQL_IPTRACE_RESULT=-1
9 OPTIONS SQL_IP_TRACE=(NONE);
10 QUIT; RUN;
11

[ChrisNZ · Accepted solution]

@Nikhil28 Please try harder.

You removed the semi-colon when transforming my generic statement into a SQL variable formula. 

You also need to remove the value assignment syntax to build your SQL query

SELECT  input( COMPRESS(t1.NON_WAREHOUSE_UOM, ,'dk') , 32.)  AS CALCULATION

You obviously know how to write SQL, so please don't ask us to guide you through each baby step.  🙂

[Shmuel]

assuming the number has no decimal digits then use otherwise adapt informat

    num = input(compress(text,,'kd') , best8.);

[Shmuel]

'kd' stands for Keep Digits