About Sandeep77

Sandeep77 · ‎06-08-2022

Actually, I tried with left join and inner join both to see if that works but none worked.

Sandeep77 · ‎06-08-2022

Thank you for the information. I did changed that and it gives me the same name and account number information. I feel that I am also doing something wrong with the inner join SS.Main_data as it is not joining. Can you please check the inner join statement and if possible let me know why it is not joining with imported file data.

Sandeep77 · ‎06-08-2022

Hi Experts, I have imported a file with account number and name. Now I want to add remaining information from an existing dataset. So I have created a libname where the dataset is stored but I am not sure how to join both the data. I am doing as the code mentioned but it is only giving information about file 1 which just have the account number and name. Can you please suggest what changes do I need to make in my code. Thank you. libname SS '\\local\shares\SASWork\SASUSERS\SSD'; Proc sql; create table Adding_info as select a.* from work.file_1 as a left join SSD.Main_Data as b on a.accountnumber=b.accountnumber; quit;

Sandeep77 · ‎05-31-2022

I am not very sure about it. I first imported a file and then created a table. Then I am trying to add the rep code information to those account number but it's not working. proc sql; create table Accounts_left_Trace AS Select account_code From work.acc_left_trace; (acc_left_trace is the file that I mported in SAS) quit; proc sql; create table Add_repcode AS Select a.*, b.rep_code From work.acc_left_trace inner join p2scflow.debt as b on a.debt_code = b.debt_code; quit;

Sandeep77 · ‎05-31-2022

Hi Experts, I have imported the data in SAS. Now I am trying to add few more information but I am getting error as unresolved reference. Please guide me as I am a new user. proc sql; create table Add_repcode AS Select a.*, b.rep_code From work.acc_left_trace inner join p2scflow.debt as b on a.debt_code = b.debt_code; quit; I am getting below error: 29 proc sql; 30 create table Add_repcode AS 31 Select a.*, 32 b.rep_code 33 From work.acc_left_trace 34 inner join p2scflow.debt as b on a.debt_code = b.debt_code; ERROR: Unresolved reference to table/correlation name a. ERROR: Could not expand a.*, correlation name not found. ERROR: The following columns were not found in the contributing tables: a. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 35 quit; NOTE: The SAS System stopped processing this step because of errors.

Sandeep77 · ‎05-31-2022

I did tried doing that but it gave me unresolved reference and few more errors. 9 proc sql; 30 create table Add_repcode as 31 select a.*, 32 b.rep_code 33 From Test 34 inner join p2scflow.debt as b on a.account_code = b.account_code ; ERROR: Unresolved reference to table/correlation name a. ERROR: Could not expand a.*, correlation name not found. ERROR: The following columns were not found in the contributing tables: a. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 35 quit; NOTE: The SAS System stopped processing this step because of errors.

Sandeep77 · ‎05-31-2022

I am trying to get a data from the previous table using from statement but getting error. Please suggest experts. proc sql; create table Test AS Select Account_code From work.'foreign address'n; quit; proc sql; create table Add_repcode as select a.*, b.rep_code, From Test inner join p2scflow.debt as b on a.Account_code= b.Account_code; quit; Getting this error 30 proc sql; 31 create table Add_repcode as 32 select a.*, 33 b.rep_code, 34 From Test _________________ 79 ERROR 79-322: Expecting a FROM. 35 inner join p2scflow.debt as b on a.account_code = b.account_code ; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 36 quit;

Sandeep77 · ‎05-27-2022

I tried making those changes Proc import datafile='\\test.local\shares\public\Full_manual_trace_book.csv' out=Manual_trace_full dbms=csv replace; run; proc sql; create table Manual_tracing as select a.*, b.debt_code, b.tran_code, c.td_altdescrn, c.td_stddescrn from p2scflow.debt_trans as b inner join manual_trace_full as a on a.debt_code = b.debt_code inner join p2scflow.transdescrn as c on b.Tran_code = c.Tran_code where b.tran_code in ('SO971','SO972') order by b.debt_code; quit; I am getting this error now: 29 proc sql; 30 create table Manual_tracing as 31 32 select a.*, 34 b.debt_code, 35 b.tran_code, 42 c.td_altdescrn, 43 c.td_stddescrn 44 45 from p2scflow.debt_trans as b 46 inner join manual_trace_full as a on a.debt_code = b.debt_code 47 inner join p2scflow.transdescrn as c 48 on b.Tran_code = c.Tran_code 49 where b.tran_code in ('SO971','SO972') 50 51 52 ; ERROR: Expression using equals (=) has components that are of different data types. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 2 The SAS System 13:44 Thursday, May 26, 2022 53 quit;

Sandeep77 · ‎05-27-2022

Hi Experts, I have imported a file in SAS and now adding few more details in that file but I am getting the below error. I feel it's a issue in joining. Please suggest Error Log: 29 proc sql; 30 create table Manual_tracing as 31 32 select a.*, 33 34 b.debt_code, 35 b.tran_code, 36 b.tx_date, 37 b.tx_time, 38 b.dt_tx_date, 39 b.tx_manauto, 40 b.op_code, 41 b.tx_char_i11, 42 c.td_altdescrn, 43 c.td_stddescrn 44 45 from p2scflow.debt_trans as b 46 inner join b on a.debt_code = b.debt_code 47 inner join p2scflow.transdescrn as c 48 on b.Tran_code = c.Tran_code 49 where b.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748') 50 51 order by a.debt_code 52 ; ERROR: File WORK.B.DATA does not exist. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 2 The SAS System 13:44 Thursday, May 26, 2022 53 quit; Proc import datafile='\\test.local\shares\public\Full_manual_trace_book.csv' out=Manual_trace_full dbms=csv replace; run; proc sql; create table Manual_tracing as select a.*, b.debt_code, b.tran_code, c.td_altdescrn, c.td_stddescrn from p2scflow.debt_trans as b inner join b on a.debt_code = b.debt_code inner join p2scflow.transdescrn as c on b.Tran_code = c.Tran_code where b.tran_code in ('SO971','SO972') order by b.debt_code; quit;

Sandeep77 · ‎05-27-2022

Hi Experts, I have exported a CSV file from a location to SAS and now I am adding some information from the database. But I am not able to link that CSV file information from the database. I am missing something to link the information. Can you please guide? Here is the import step: Proc import datafile='\\test2.local\shares\public\Trace\Full_manual_trace_book.csv' out=Manual_trace_full dbms=csv replace; run; Then I run this code to add further information to the accounts in imported file proc sql; connect to oledb (provider=sqlncli11.1 properties = ("Integrated Security" = SSPI "Persist Security Info" = False "Initial Catalog" = pinsys prompt = NO "Data Source" = 'ELECTRA' read_lock_type = no)); create table Manual_tracing as select t2.debt_code, t2.tran_code, t2.td_altdescrn, t2.td_stddescrn from (select * from connection to oledb (select b.debt_code, b.tran_code, c.td_altdescrn, c.td_stddescrn from [Pinsys].[dbo].[debt_trans] as b inner join [Pinsys].[dbo].[transdescrn] as c on b.Tran_code = c.Tran_code where b.tran_code in ('JO741','JO742',);)) as t2 order by t2.debt_code; quit; When I run the 2nd code, it takes the data from the database in thousands but my file just have few accounts. I am looking to add those extra information to those few accounts.

Sandeep77 · ‎05-13-2022

Hi Experts, I am trying to filter on a date column but not getting the result as expected. It gives the same result if I run without that filter. I want tx_date prior to the payment date. Here is the code: proc sql; create table Collection as select distinct a.*, b.paymentdate, b.tx_date, b.tran_code, sum(b.tx_amount) as Collected from Payment_plans_full as a inner join ABC.debt_trans as b on a.accountnumber=b.debt_code where b.tx_date between '23SEP2021'd and '25NOV2021'd and b.tx_date >= b.paymentdate and (b.tran_code like 'DR%' and b.tran_code not in ('DR3109', 'DR3108')) group by a.accountnumber ; quit; Can you please suggest?

Sandeep77 · ‎04-12-2022

Hello Experts, The error says expecting a ','. But I am not sure what is that. Can you please check and clarify it? 30 proc sql; 31 select a.*, 32 b.accountstatuskey_current, 33 b.accountstatuskey_previous, 34 b.Datekey as Movement_Date date9. _____ 78 76 ERROR 78-322: Expecting a ','. ERROR 76-322: Syntax error, statement will be ignored. 35 from work.add_repcode as a 36 inner join DWHDW.fact_tx_AccountStatus as b on a.Accountkey = b.Accountkey; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 37 quit; NOTE: The SAS System stopped processing this step because of errors.

Sandeep77 · ‎04-12-2022

Thank you!

Sandeep77 · ‎04-12-2022

Hello Experts, I am using an existing code which runs okay but when I am adding where b.client_code like "%Vanquis%", I am getting ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be completed.: Statement(s) could not be prepared.: Invalid column name '%Vanquis%'. (from [Pinsys].[dbo].[debt_trans] as b 65 inner join [Pinsys].[dbo].[transdescrn] as c 66 on b.Tran_code = c.Tran_code 67 where b.tx_date >= &Start_trace and 68 b.tx_date <= &End_Trace and 69 b.client_code like "%Vanquis%" WARNING: Apparent invocation of macro VANQUIS not resolved. 70 71 and b.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748');)) as t2 72 73 order by t2.debt_code, t2.tx_date 74 ; ERROR: Describe error: IColumnsInfo::GetColumnInfo failed. : Deferred prepare could not be completed.: Statement(s) could not be prepared.: Invalid column name '%Vanquis%'. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 75 quit;) I am using the below code: proc sql; connect to oledb (provider=sqlncli11.1 properties = ("Integrated Security" = SSPI "Persist Security Info" = False "Initial Catalog" = pinsys prompt = NO "Data Source" = 'ELECTRA' read_lock_type = no)); create table Manual_table as select t2.debt_code, t2.tran_code, datepart(t2.tx_date) as Trace_date format date9., input(t2.tx_time,time8.) as trace_time format time8., t2.dt_tx_date as Trace_dt, t2.tx_manauto, t2.td_stddescrn from (select * from connection to oledb (select b.debt_code, b.tran_code, b.tx_date, b.tx_time, b.dt_tx_date, b.tx_manauto, b.client_code from [Pinsys].[dbo].[debt_trans] as b inner join [Pinsys].[dbo].[transdescrn] as c on b.Tran_code = c.Tran_code where b.tx_date >= &Start_trace and b.tx_date <= &End_Trace and b.client_code like "%Vanquis%" and b.tran_code in ('MO9741','MO9742','MO9743','MO9744','MO9745','MO9746','MO9747','MO9748');)) as t2 order by t2.debt_code, t2.tx_date ; quit;

Sandeep77 · ‎04-11-2022

Hello Experts, I am trying to join Test_1 with Test_2 table with common client_code which is available in both the table but getting below error: NOTE: Writing HTML5(EGHTML) Body file: EGHTML 28 29 proc sql; 30 create table Test_2 as 31 select a.*, 32 b.cl_name, 33 b.client_code 34 from Test_1 as a 35 inner join p2scflow.client as b on a.client_code = b.client_code; ERROR: Column client_code could not be found in the table/view identified with the correlation name A. ERROR: Column client_code could not be found in the table/view identified with the correlation name A. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 36 quit; NOTE: The SAS System stopped processing this step because of errors. Not sure why it is throwing this error message. Can you please suggest what I am doing wrong here?

Online Status	Offline
Date Last Visited	‎07-23-2024 11:37 AM

Re: Comparing similar datasets and finding the difference between the ...

Re: Comparing similar datasets and finding the difference between the ...

Re: Comparing similar datasets and finding the difference between the ...

Comparing similar datasets and finding the difference between the two ...

Re: Comparing two excel files to find the different values

Re: Comparing two excel files to find the different values

Comparing two excel files to find the different values

SAS macro query

Macro is not showing the correct output

The macro is not working.

Re: Macro is not showing the correct output

Re: The macro is not working.

Re: The macro is not working.

Re: In operator does not work

Re: Finding if the Accounts are available in the datasets

Re: Syntax error

Re: Library

Re: Case when condition

Re: Joining imported data with existing dataset.

Re: Joining imported data with existing dataset.

Joining imported data with existing dataset.

Re: Adding information to imported data set.

Adding information to imported data set.

Re: From statement error

From statement error

Re: Joins

Joins

Joining the datasets

where statement not giving the exact results.

ERROR 78-322: Expecting a ','.

Re: where statement

where statement

Inner join