BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AJDELACRUZ0220
Fluorite | Level 6

Hello,

 

I am trying to extract information from Oracle database using the following command:

 

proc sql;

connect to odbc as hindsight (datasrc='PostgreSQL30' user=xxxxx password="xxxx");

create table value04  as

select * from connection to hindsight

(SELECT fact_event.fact_id,fact_event.region_cd,fact_event.transaction_dte,fact_event.roll_num,

fact_event.property_cd,fact_event.property_desc,fact_event.cty_mun,value_dim.value_rounded, comp_dim.value_component_id,

comp_dim.transaction_dte, comp_dim.unadjusted, comp_dim.value, comp_dim.valuation_type, comp_dim.priority,

comp_dim.property_adjusted, comp_dim.component_adjusted, comp_dim.component_id, comp_dim.building_id_number,

comp_dim.variables, comp_dim.active

FROM hindsight_3.fact_event fact_event

INNER JOIN

(SELECT roll_num, max(transaction_dte) as max_transaction_dte

FROM hindsight_3.fact_event fact_event

WHERE fact_event.event_type='CMV' and fact_event.status_cd='A'

GROUP BY roll_num) transaction_dte on fact_event.roll_num=transaction_dte.roll_num and fact_event.transaction_dte=transaction_dte.max_transaction_dte

LEFT JOIN hindsight_3.property_dim property_dim ON fact_event.fact_id = property_dim.fact_id

LEFT JOIN hindsight_3.value_component_dim comp_dim ON fact_event.fact_id = comp_dim.fact_id

LEFT JOIN hindsight_3.value_dim value_dim ON fact_event.fact_id = value_dim.fact_id

WHERE ((substring(fact_event.region_cd,1,1) = '0' and substring(fact_event.region_cd,2,1) = '04') or fact_event.region_cd = '04')

and fact_event.event_type='CMV' and fact_event.status_cd='A' and property_dim.status_cd='A'

ORDER BY fact_event.roll_num);

quit;

run;

 

When I checked the "variables" column the data length is set to 1024 characters only whereas the original length of the variable is 65535.

 

Can you please let me know how to extract the complete "variables" column?

 

Thank you,

 

Ave

 

1 ACCEPTED SOLUTION
3 REPLIES 3
AJDELACRUZ0220
Fluorite | Level 6

this perfectly worked! thank you so much!!!


@Miracle wrote:

try dbmax_text= in your proc sql

Using SAS® to Manage and Report Long Text Fields in a Clinical DBMS


 

Kurt_Bremser
Super User

First of all, you won't be able to conserve the whole length, because the maximum length for a SAS character variable is 32767.

So it's either that SAS sees that the variable is too long and sets 1024 on it's own, or it might be that ODBC itself or the Oracle ODBC driver has a 1024 character limit.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 5090 views
  • 1 like
  • 3 in conversation