SAS Support Communities

Steelers_In_DC · ‎12-30-2024

I am using a pass through connection to sql server. I am attempting to translate from a pass through connection to hadoop. Can anyone look a the code below and see if this looks correct. The normal way I use the pass through to sql server isn't working due to an extra open parenthesis, but when I remove it I still get an error. The open parens prior to select distinct, as well as the open parens in front of WITH are the issue. If I add another close parens at the end it errors. proc sql; connect to sqlsvr (dsn=chsrproddb &_log_host); CREATE TABLE FullData AS SELECT * FROM CONNECTION TO sqlsvr (select distinct (WITH this_srvc_auth AS ( SELECT

Steelers_In_DC · ‎12-14-2023

this is not ODBC: proc sql; connect to hadoop (READ_METHOD=HDFS /* this attemps HDFS read which is faster than JDBC */ server='' /* the hiveserver we talk to */ LOGIN_TIMEOUT=300 schema=&schema. /* this is the schema where you want to read or write data to */ uri=

Steelers_In_DC · ‎12-14-2023

I found the below solution in our community but would like to adapt it to include columns: this is passing through to hadoop: select * from connection to hadoop (show tables); Is there a modification that can show tables and columns?

Steelers_In_DC · ‎09-29-2023

I have some legacy code that takes a very long time to run, on join is > 8 hours. When I break it down it has many or and multiple filters: proc sql; create table have as select a.*,b.* from table 1 a, table 2 b where a.id = b.id and blah or where a.id = b.id and blah blah blah I've broken this apart into 6 different queries. I concatenate at the end in a dataset and it runs so much faster I'm shocked. From hours to minutes. I can't find much on using or like this other than it's not efficient. Is there any rules/laws/best practices/documentation somewhere that details this? I'd like to have more than my test to take back to the original developers to get them to change their ways. Thank You in advance

Steelers_In_DC · ‎03-10-2022

Hello all, I have very little tech/admin support in my current role. I am trying to find the name of the edge node I am connected to so I can connect elsewhere(putty,crt,jupyter). Is there a way within SAS to see the edgenode? I find some documentation about %macro allPaths() but it hasn't been helpful. Thank You in advance.

Steelers_In_DC · ‎10-21-2021

I see the issue, case is before the set statement so it's a number. I added format case $500.; after the data step and this works. thanks for the help.

Steelers_In_DC · ‎10-21-2021

this looks great, can up modify this for case as a character?

Steelers_In_DC · ‎10-21-2021

There is also an ID field, I can use: data want; set have; by id; retain _case; if not missing(case_id) then _case = case_id; run; that gets me part of the way, but it fills in the last rows when the next value will not be the same. I suppose I could sort the other way and delete some.

Steelers_In_DC · ‎10-21-2021

I have a file with a case number that is sporadically filled in. The file is 500k plus rows, If the next time a case # is filled in happens to be the same case # as before I want to fill in the blanks. I thought of using lag() but some of the missing rows are pretty large. If the next case # populated is different the rows between should be left blank. data have; infile cards dsd; input case$ ; cards; 500 , , , 500 , 600 , , 700 700 , 700 ; run; data want; infile cards dsd; input case$ ; cards; 500 500 500 500 500 , 600 , , 700 700 700 700 ; run;

Steelers_In_DC · ‎10-15-2021

I'm always open to suggestions but in case this can help anyone else I'm going to put my solution below: /*identifies word case in string*/ data p_case; set check_case(obs=10 keep=Event_Log_Derived_Details); if _N_ > 0 then do; p_case = prxparse("/Case/"); end; find_case = prxmatch(p_case,Event_Log_Derived_Details); run; /*uses place of 'Case' in string to get substring with '/' as the delimiter to extract the desired ID 'case' number*/ data find_case; set p_case; case = scan(substr(Event_Log_Derived_Details,find_case),2,'/'); run;

Steelers_In_DC · ‎10-15-2021

I have a long alpha numeric string with front slash delimiters throughout. Within the string are specific 'case' id numbers. I am trying to get everything in the string after the word 'Case', after that I can use the delimiter to drop everything after that. I'm so out of practice with regex that I haven't been able to get there searching. any help is appreciated.

Steelers_In_DC · ‎12-18-2020

Spot on, I didn't realize I missed one of the (pre transpose) columns in formatting. Once I fixed that it's perfect, and the formatting held through the proc report. Have a great weekend, Happy Holidays, stay healthy.

Steelers_In_DC · ‎12-18-2020

I have a simple transpose from wide with 11 columns to tall with 3 columns. When the data is wide the numbers are formatted to show commas, when tall they lose the formatting. Is there anyway in the proc to format the columns? I'm following up with a proc report, so if I can format there I'll be happy to do it there.

Steelers_In_DC · ‎05-06-2020

I have a dataset that appears to have two duplicate columns. One is named _col15, the other is _col18, they both match other columns that I have pulled in from a larger dataset. I've never seen this before, and don't see anything in the code that would generate this. Prior to this dataset there are several simple left joins, nothing else. Any thoughts? Edit: apologies, I found in the original query that two columns were entered twice, the forum wouldn't let me delete the post.

Steelers_In_DC · ‎04-23-2020

I'm not sure where in the query process these commands go, would this be after the : PROC SQL; CONNECT TO odbc(); (enter here) and before the: CREATE TABLE table AS

Online Status	Offline
Date Last Visited	‎12-30-2024 01:51 PM

SAS Support Communities

sql server using 'with'

Re: hive show table and column

hive show table and column

proc sql using or

find edge node

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Fill in rows with previous value if next value is the same.

Re: reg ex extract

Re: proc sql using or

Re: Adding quotes to a Macro Variable

Re: How to run a program in EG5.1 without connecting to a server

Re: macro variable in set statements based on today()

Finding all single linked chains: the allChains macro

find edge node

infile 'red x' with no error in log

Re: Extracting Month, Year and quarter

Re: Which Date function can be used to extract month(Character) from t...

Re: How to assign a column value based on another column ??

sql server using 'with'

Re: hive show table and column

hive show table and column

proc sql using or

find edge node

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Re: Fill in rows with previous value if next value is the same.

Fill in rows with previous value if next value is the same.

Re: reg ex extract

reg ex extract

Re: proc transpose wide to tall, numbers lose formatting

proc transpose wide to tall, numbers lose formatting

duplicated columns

Re: Hive Statements with PROC SQL

Follow Us

What is...