Steelers_In_DC Tracker

sql server using 'with'

Steelers_In_DC — Mon, 30 Dec 2024 15:18:48 GMT

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

Re: hive show table and column

Steelers_In_DC — Thu, 14 Dec 2023 18:34:55 GMT

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=

hive show table and column

Steelers_In_DC — Thu, 14 Dec 2023 17:53:15 GMT

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?

proc sql using or

Steelers_In_DC — Fri, 29 Sep 2023 15:00:06 GMT

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

find edge node

Steelers_In_DC — Thu, 10 Mar 2022 15:48:10 GMT

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.

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

Steelers_In_DC — Thu, 21 Oct 2021 21:00:26 GMT

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.

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

Steelers_In_DC — Thu, 21 Oct 2021 19:55:47 GMT

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

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

Steelers_In_DC — Thu, 21 Oct 2021 18:03:49 GMT

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.

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

Steelers_In_DC — Thu, 21 Oct 2021 17:31:59 GMT

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;

Re: reg ex extract

Steelers_In_DC — Fri, 15 Oct 2021 14:06:42 GMT

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;

reg ex extract

Steelers_In_DC — Fri, 15 Oct 2021 13:52:13 GMT

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.

Re: proc transpose wide to tall, numbers lose formatting

Steelers_In_DC — Fri, 18 Dec 2020 21:31:23 GMT

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.

proc transpose wide to tall, numbers lose formatting

Steelers_In_DC — Fri, 18 Dec 2020 21:17:20 GMT

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.

duplicated columns

Steelers_In_DC — Wed, 06 May 2020 15:54:29 GMT

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.

Re: Hive Statements with PROC SQL

Steelers_In_DC — Thu, 23 Apr 2020 18:57:02 GMT

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

Re: find EG IP address using PC SAS

Steelers_In_DC — Fri, 10 Jan 2020 18:28:12 GMT

could this be helpful in anyway? I'm not sure if this tells you if EG is connected the way you are talking about, here is the output from setinit:

Base SAS Software

SAS/STAT

SAS/GRAPH

SAS/ETS

SAS/FSP

SAS/OR

SAS/AF

SAS/IML

SAS/SHARE

SAS/ASSIST

SAS/CONNECT

SAS/EIS

SAS/GIS

SAS/SHARE*NET

MDDB Server common products

SAS/Secure 168

SAS/Secure Windows

SAS Enterprise Guide

OR OPT

OR PRS

OR IVS

OR LSO

SAS/ACCESS Interface to SAP ASE

SAS/ACCESS Interface to PC Files

SAS/ACCESS Interface to ODBC

SAS/IML Studio

SAS Workspace Server for Local Access

High Performance Suite

Re: find EG IP address using PC SAS

Steelers_In_DC — Fri, 10 Jan 2020 18:19:15 GMT

I understand that, I tried to run proc setinit; but it doesn't run, just sits there and thinks for a long time. I'm not sure what license I have in this seat, I'm working on figuring that out.

Re: find EG IP address using PC SAS

Steelers_In_DC — Fri, 10 Jan 2020 17:48:14 GMT

Is there anyway to print the log that goes to the object spawner while I am using PC SAS, I'm at a new client with little support getting connected, so I'm trying to pull the info from PC SAS to enter into EG I see the folder for integrated technologies but nothing in there that I can use.

find EG IP address using PC SAS

Steelers_In_DC — Fri, 10 Jan 2020 16:56:47 GMT

This is a duplicate message but the original is several years old and I wasn't able to follow it, so I thought it was worth repeating.

I am new to a client and trying to connect EG.

I have PC SAS but nobody here uses EG, I'd like to use PC SAS and print the IP address to the log. The idea is that I would get the IP address and port from the log in PC SAS, and use that to connect with EG. I hope that makes sense, any help is appreciated.

Re: Adding quotes to a Macro Variable

Steelers_In_DC — Thu, 23 May 2019 13:07:04 GMT

I'm a big fan of this solution, it helps to use a macro variable date within teradata.

Excellent!