Desktop productivity for business analysts and programmers

Changing a variable from NUM to CHAR in PROC SQL

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

Changing a variable from NUM to CHAR in PROC SQL

Hey there, i'm trying to Left Join a variable but the column I'm matching it to are different data types. WHat is the best way to format it?

I tried to format ATSo.odr_number using $13.  but i keep getting errors. Should I use a Proc Format before this?

PROC SQL;

CREATE TABLE work.add_class AS

  SELECT ac.date_shipped, ac.order_id, ac.brand_code, ac.customer_id,

  ATSo.classification, ATSo.brand_code AS ATSbrand

  FROM work.add_cust AS ac

  LEFT JOIN work.TEMP_ORDERS AS ATSo

  ON ac.invoice_number = ATSo.odr_number $13.;

;

RUN


Accepted Solutions
Solution
‎09-10-2014 02:22 PM
Trusted Advisor
Posts: 1,228

Re: Changing a variable from NUM to CHAR in PROC SQL

Use proc contents to check variable types. if ac.invoice_number is char then try this.

ON input(ac.invoice_number,8.) = ATSo.odr_number ;

View solution in original post


All Replies
Solution
‎09-10-2014 02:22 PM
Trusted Advisor
Posts: 1,228

Re: Changing a variable from NUM to CHAR in PROC SQL

Use proc contents to check variable types. if ac.invoice_number is char then try this.

ON input(ac.invoice_number,8.) = ATSo.odr_number ;

Regular Contributor
Posts: 233

Re: Changing a variable from NUM to CHAR in PROC SQL

proc sql;

create table work.add_class as

  select ac.date_shipped, ac.order_id, ac.brand_code, ac.customer_id,atso.classification, atso.brand_code as atsbrand

   from work.add_cust as ac left join work.temp_orders as atso

    on ac.invoice_number = put(atso.odr_number,char13.);

quit;

Example:

data have1;
input id $ var $;
cards;
1 a
2 b
3 c
;
run;

data have2;
input id var $;
cards;
1 a
2 b
3 c
;
run;

proc sql;
create table want as
select a.* from have1 a left join have2 b on a.id=put(b.id,char1.);
quit;

proc contents data=want;
run;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 359 views
  • 1 like
  • 3 in conversation