gyambqt Tracker

How to write a SAS program to find relationship between nodes

gyambqt — Mon, 26 Feb 2024 00:06:13 GMT

I have two tables:

Table1 and Table2

Data in Table1 can be like:

Table_a Table_b

A B
A C
C E
B D

Data in table2 can be like:

Table_name Status
A Completed
B NOTCOMPLETED
C COMPLETED
D NOTCOMPLETED
E OK

my output should be like:
Table1 status1 Table2 status2 table3 status3
A Completed B NOTCOMPLETED D NOTCOMPLETED
A Completed C COMPLETED E OK

I want to write all relations in one single row

How to conduct impact analysis of a metadata table using SAS code

gyambqt — Tue, 07 Nov 2023 00:31:58 GMT

I have a list of metadata tables registered in SAS Data Integration Studio within a specified library. Is there a method to write SAS code that generates a list including each table's name and the corresponding SAS DI jobs that utilize the table

Re: Missing date column load data from Oracle to SAS

gyambqt — Mon, 02 Oct 2023 09:06:18 GMT

The date from Oracle can be read into SAS properly for value like:
31/DEC/9999 11:59:59.000000000 PM

Missing date column load data from Oracle to SAS

gyambqt — Mon, 02 Oct 2023 08:46:40 GMT

I have an Oracle table called Oracle_table.

In this Oracle table I have only one column and one row

Column name: Datetime_1

Value: 31/DEC/2801 11:59:59.000000000 PM

I load the data from Oracle into SAS using data steps

Data T;

set Schema.Oracle_table; /* assume implicit pass through*/

run;

Proc print data=T;

run;

I got a . (dot) for the column Datetime_1 in SAS

Why?

Transfer text files from remote SFTP sever to local linux server

gyambqt — Wed, 09 Aug 2023 01:32:11 GMT

Hi Experts,

I am willing to transfer files from a remote SFTP server to local linux server and my command is below:

Is there any problem with my code below as an example?

method1:

%let ftpcmd = "sftp -i path of SSHKEY username@sftpserverdetail:/sftppath/file.csv /localpath/file.csv";
filename ftppipe pipe &ftpcmd.;

data _null_;
infile ftppipe;
input;
put _infile_;
run;

method2:

%let ftpcmd = "scp username@sftpserverdetail:/remotesftppath/file.csv /localpath/file.csv";
filename ftppipe pipe &ftpcmd.;

data _null_;
infile ftppipe;
input;
put _infile_;
run;

How to write a SAS code to extract SAS DI metadata objects?

gyambqt — Wed, 26 Jul 2023 00:30:40 GMT

Hi Experts,

Is there any code I can borrow to extract SAS DI metadata objects such as metadata name, metadata location etc?

for example:

/myfolder/Job1

I want to extract the metadata path and objects stored under this path?

I understand there is a tool called SAS list Object can be used but I would like to know if anyone has available SAS macro to do this?

How to identify log files differences between two log files?

gyambqt — Fri, 17 Feb 2023 23:39:00 GMT

For example I have two log files: A and B

A has

B has

So I want to compare the two files line by line and put the difference in another table such as

tablename=Compare_result

line_no records_from_a records_from_b

1 a c

Re: SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 19:47:54 GMT

They are all character with 5000 lengths. All same type

Re: SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 06:24:36 GMT

Re: SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 04:00:16 GMT

Yes loading without bulk load is working perfect

SAS bulkload issue

gyambqt — Sun, 25 Sep 2022 02:36:35 GMT

Hi Experts,

Is there any limitation on number of columns used when bulkload into Oracle?

I am loading a SAS table into Oracle using below:

proc append base = oracle.table2(&oracle_bload_options BULKLOAD = YES
BL_LOAD_METHOD = Truncate )
data =table1 force ;

run;

table1 and Oracle.table2 both have 600 columns

I get the below errors:

ERROR:
**********************************************************************
Please look in the SQL*Loader log file for the load results.
SQL*Loader Log File location(available only if BL_DELETE_FILES=NO was set) : --
Note: In a Client/Server environment, the Log File is located on the Server. The log file is also echoed in the Server SAS log
file.
**********************************************************************
NOTE: Statements not processed because of errors noted above.
NOTE: PROCEDURE APPEND used (Total process time):
real time 5.61 seconds
user cpu time 0.04 seconds
system cpu time 0.03 seconds
memory 13503.21k

however if I reduce columns from Oracle.table from 600 to 260 then it is working!

Only first few columns have values and all rest columns are empty

Why is this?

Re: How to optimize the code to compare two tables with large number of columns

gyambqt — Thu, 22 Sep 2022 23:01:39 GMT

I have modified my questions. The output wanted can be the difference rows between two tables.

How to optimize the code to compare two tables with large number of columns

gyambqt — Thu, 22 Sep 2022 23:05:08 GMT

Hi Experts,

I have two tables:

Table1 and Table2

Each table has 1000 columns (column1....column100) and each column has been defined as character with 5000 length

Columns in each table are 100% identical in type and length

95% of the columns in each table empty and no value

I would like to compare two tables to see if there is any difference in data.

For example: table1 has rows which not existed in table2 or table2 has rows which not existed in table1 such as:

Proc sql;
create table diff1 as
select * from table1
EXCEPT
select * from table2
;
quit;

Proc sql;
create table diff2 as
select * from table2
EXCEPT
select * from table1
;
quit;

Proc sql noprint;
select sum(Diff_Count) into :TotalChanges from
(
(select count(*) as Diff_Count from diff1)
union all
(select count(*) as Diff_Count from diff2)
) x
;
quit;

(I want &TotalChanges above)

last condition is table1 always has data but table2 sometimes empty

How to program this most efficiently due to large number of columns and length?

Re: SAS DI question

gyambqt — Tue, 12 Apr 2022 09:08:34 GMT

The parallel option of the loop was not enabled. It is pretty much running and passing parameter one by one. Even though, the loop is still running even it hits an error

DI Studio loop question

gyambqt — Tue, 12 Apr 2022 13:52:29 GMT

I am wondering how do you stop DI processing if there is an error within the loop start and loop end.

For example:

my DI jobs like below:

table1--->loop start--->job1--->job2--->jobN---->loop end

table1 is used to pass parameters to job1 for processing

however I found even job1 result any error it will not stop the loop which means job2 ...Job3....JobN still gets executed.

my question is how do you stop loop processing immediately if any of the middle jobs failed?

SAS DI Loop question

gyambqt — Tue, 12 Apr 2022 08:09:25 GMT

I am wondering how do you stop DI processing if there is an error within the loop start and loop end.

For example:

my DI jobs like below:

table1--->loop start--->job1--->job2--->jobN---->loop end

table1 is used to pass parameters to job1 for processing

however I found even job1 result any error it will not stop the loop which means job2 ...Job3....JobN still gets executed.

my question is how do you stop loop processing immediately if any of the middle jobs failed?

Re: SAS DI, how to creatework table like a registered table?

gyambqt — Wed, 12 Jan 2022 12:16:59 GMT

How do you register work table?

SAS DI, how to creatework table like a registered table?

gyambqt — Tue, 11 Jan 2022 22:54:56 GMT

In SAS DI, for some transformation you will see work table represented by a green rectangular box. Is it possible to create the work table just like registered table? so instead of seeing rectangular box as work table, I want to see physical table feature for work table.

Re: Why SAS is converting . into blank value when read from CSV?

gyambqt — Wed, 15 Dec 2021 07:04:21 GMT

They are the same column

Re: Why SAS is converting . into blank value when read from CSV?

gyambqt — Wed, 15 Dec 2021 05:03:22 GMT

input Financial_Year;