About SASAna

SASAna · ‎04-23-2019

1) %put is not printing the numbers. 2) log of error - 30303 proc sql noprint; update <table> set CLM_CNT = 30303 ! INPUT(&&run_type._CLM_COUNT.,4.) , CLMS_CNT = INPUT(&&run_type._CLM_LINE_COUNT.,4.) , _ 22 30303 ! MEMBER_CNT ERROR 22-322: Syntax error, expecting one of the following: a name, *. SYMBOLGEN: && resolves to &.

SASAna · ‎04-23-2019

Need help ! I am getting error while running query and %put is not printing the values. I am building some counts and loading into Oracle tables' numeric fields. PROC SQL noprint; Create Table output_Stats as Select count(distinct(CLMS)), count(CLMS), count(distinct(MBRS)), count(distinct(PROVS)), sum(DOLLARS) into :&run_type._CLM_COUNT, :&run_type._CLMS_COUNT, :&run_type._MEM_COUNT, :&run_type._PROV_COUNT, :&run_type._DOLLARS from output_x; Quit; %put &=&&run_type._CLM_COUNT &=&&run_type._CLMS_COUNT &=&&run_type._MEM_COUNT &=&&run_type._PROV _COUNT &=&&run_type._DOLLARS; /* Note: Oracle table fields are numeric attributes */ %If &run_type = R %then %do; proc sql noprint; update <Oracle_table> set CLM_CNT = INPUT(&&run_type._CLM_COUNT.,4.) , /* i tried loading direct, but it was telling char error */ CLMS_CNT = INPUT(&&run_type._CLM_LINE_COUNT.,4.) , MEM_CNT = INPUT(&&run_type._MEMBER_COUNT.,4.) , PROV_CNT = INPUT(&&run_type._PROVIDER_COUNT.,4.) where <condition> ; Quit; %end;

SASAna · ‎04-12-2019

Hi SAS Team, I am using ods tagsets.excelxp for Excel data reporting and the sheet has bigger cell sizes than the value. How to fix this? ods tagsets.excelxp file="test.xls" style = minimal headtext="<style> td {mso-number-format:\@}</style>" options(embedded_titles='yes' sheet_name="Summary"); ODS LISTING CLOSE; proc report data = output_data ; column _all_; title; run; Thanks, Ana

SASAna · ‎12-13-2018

Hello SAS Users, I wanted some help to clear this error. I am merging SAS dataset leftouter join to ORacle table (many to one join), I am getting the dates error. How to resolve this? PAID_DT variable is DATE format in oracle table. Libname test oracle user = &MyId orapw=&mypwd path = "&Mydb" schema = Test Oracle_73 = NO; proc sql; create table select a.* , case when b.CLM_ID is null then '0' else '1' end as test_case from output_all a left outer join test.sum_oracle_table (DBKEY=CLM_ID DBSASTYPE=(PAID_DT=DATE)) b on put(datepart(b.PAID_DT),yymmddd10.) between '2017-10-01' and '2018-03-31' and a.clm_id = b.clm_id ; ERROR: Error fetching from cursor. ORACLE error is ORA-01843: not a valid month.

SASAna · ‎11-15-2018

Hi SAS users, I wanted some help and tips on how to put this process in 'parallel processing'. I am passing 15 parts - each part has 5 million records in the data extract.. after that each process will run intermediate macro's and holds in the datasets and finally load data into Database. process is running over 2 days. how to put this process in parallel run by parts to speedup the process. %Macro Exec(part); %Let Cd=%Bquote('&part'); %data extract data extract; set data_extract; run; Proc Sql Noprint; Select Count(*) Into:Obs From extract; Quit; %If %Eval(&Obs Gt 0) %Then %Do; %Loop( Obs=&Obs, Num_Per_Loop=1000, Input_Dataset=extract, Variable=id, Variable_Type=C, Macro_To_Call= data_pull Output_Dataset=All_data); %Delete_Dataset(DS_Name=extract,Lib_Name=Work); %macro1 %macro2 %macro3 %macro4 %hold_for_output(dsin=All_data,dsout=all_details); %End; %Mend Exec; Thank you.

SASAna · ‎10-30-2018

Hello, is there a proc sql alternative of Oracle Merge Statement? http://www.oracletutorial.com/oracle-basics/oracle-merge/ I'm trying to update Oracle table using value in SAS dataset within proc sql. There are multiple values to be updated . As of now, the query looks like below. Update oracle_tbl set value_1 = (select value_1 from sas_dataset where id = 1) , value_2 = (select value_2 from sas_dataset where id = 1) where exists( ) and other filters. I was wondering if there is a way to speed up performance. Please suggest if any. Thanks

SASAna · ‎10-25-2018

Thank you. it worked very well.

SASAna · ‎10-25-2018

Hi SAS Users, I am getting this below error, while reading a column from a excel( abc_short_desc has 3 byte character values, and they are looking fine when i read them) , Other columns are reading and creating the macro variables, just this one column is giving the error. What i am missing? PROC SQL NOPRINT; 24 SELECT &test_short_desc INTO :missdisc separated by '~' NOTE: Line generated by the macro variable "TEST_SHORT_DESC". 24 abc_short_desc ___________ 22 ERROR 22-322: Syntax error, expecting one of the following: !, !!, &, *, **, +, ',', -, '.', /, <, <=, <>, =, >, >=, ?, AND, AS, CONTAINS, EQ, EQT, GE, GET, GT, GTT, LE, LET, LIKE, LT, LTT, NE, NET, OR, ^=, |, ||, ~=. 25 FROM miss_Data; NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 26 %LET NO_PL=&sqlobs; 27 QUIT; Thanks, Ana

SASAna · ‎08-20-2018

Thanks Reeza, but i am getting error while reading in the tabs . WARNING: Apparent symbolic reference B not resolved for TEST A&B . I tried giving the quotes as well & tried compress & tranward functions as well.

SASAna · ‎08-20-2018

Hi SAS users, I have a spread sheet with tabs named with spaces like ( TEST A&B , TEST B&C ..Etc) , Can spaces be read in ? OR is there any other solution on how to read them. %macro Excel_Read(sheet); proc import datafile = "path" OUT = &sheet DBMS=XLSX REPLACE; SHEET ="&sheet"; run; proc append base = all_data data=&sheet; run; %mend Excel_Read; %Excel_Read(TEST A&B ); %Excel_Read(TEST B&C); %Excel_Read(TEST C&D); %Excel_Read(TEST E&F); Thanks, Ana

SASAna · ‎05-31-2018

Hi SAS Team, I am trying to do transpose the below created macro variable's ( SOURCE_FIELDS, TARGET_FIELDS) into columns like below, which i am using to create dynamic table. expected transpose - source_field1 source_field2 source_field3 ......so on till 500th attribute target_field1 target_field2, target_field3 ... so on 500th attribute using in Dynamic table - proc sql; create table test_table as source_field(i) as target_field(i) from <table_joins>; quit; there are around 500 attributes with comma separated by comma in source and target fields. PROC SQL; SELECT SOURCE_FIELDS , TARGET_FIELDS INTO :SOURCE_FIELDS , INTO :TARGET_FIELDS SEPARATED BY ',' FROM LOOKUP_TABLE ; QUIT;

SASAna · ‎04-23-2018

Hi Tom, Data is very huge 800 million records, so i was going for easier way that formatting the data in SAS after the ORacle data pull. I will test CAST().

SASAna · ‎04-23-2018

Thanks Kiran. PROC APPEND worked fine and inserted the records without any error. Thanks, Ana

SASAna · ‎04-23-2018

Hello, I need some help in passing blank values in DATE fields of the end Oracle Table. I am connectting to ORacle with direct pass through option and pulling some data and mapping them to insert into another oracle table. Proc Sql; Connect To Oracle(User=&Myid Orapw=&Mypwd Path="&Mydb"); Create Table Test as Select * From Connection to Oracle (select -- 400 attributes-- NULL as paid_dt_orig ) Quit; Proc contents on Test table - 281 PAID_DT_ORIG Char 1 $. $. PAID_DT_ORIG Inserting this data into another Oracle table using libname statement. PROC SQL; INSERT INTO DBMS.&MyTable (&colnames) SELECT &colnames_a FROM Test A; %let NQ_COUNT=&SQLOBS; QUIT; %put &NQ_COUNT; I am getting the below error for all the DATEf ields where i have mapped NULL. ERROR: Value 17 on the SELECT clause does not match the data type of the corresponding column listed after the INSERT table name. Data is very huge. So I can not do SAS formattings. IS there any easier way to update Proc SQL to create a blank date field? Thanks, Ana

SASAna · ‎04-04-2018

Hi Astounding, I ran the exact change you have asked me to test on . and getting "ERROR: All positional parameters must precede keyword parameters. " %let NRML_CODE4 = '1','2','3'; %let UPCDED_CODE4 = 'A', 'B', 'C'; %let GRP_CD4 = 'X'; %macro data_pull(NRML_CODE =,UPCDED_CODE =,GRP_CD =); proc sql; create table grp_clms as select * from sashelp.cars where model in (&NRML_CODE. &UPCDED_CODE.) ; quit; %mend; %data_pull(nrml_code = &NRML_CODE4. , upcded_code = &UPCDED_CODE4., grp_cd= &GRP_CD4.);

Online Status	Offline
Date Last Visited	‎06-30-2021 01:11 AM

Re: SCD type2 in SAS

SCD type2 in SAS

Re: Proc transpose

Re: Proc transpose

Proc transpose

Re: proc gee - Nesting continuous variable

proc gee - Nesting continuous variable

Re: min Date conversions to character date

min Date conversions to character date

Re: adding weeks to the max of the date and comparing it with datetime...

Re: SCD type2 in SAS

Re: Proc transpose

Re: adding weeks to the max of the date and comparing it with datetime...

Re: adding weeks to the max of the date and comparing it with datetime...

Re: scan in the proc sql

Re: scan in the proc sql

Excel reading and converting to macro variable error

Re: Converting character date to " DATETIME20."

Re: SAS macro numeric values

SAS macro numeric values

ods tagsets.excelxp formats with bigger cells

libname SAS- Oracle dates

parallel processing

Merge in PROC SQL/Libname

Re: Excel reading and converting to macro variable error

Excel reading and converting to macro variable error

Re: spaces can be read in from the tabs of the Excel ?

spaces can be read in from the tabs of the Excel ?

proc transpose

Re: PROC SQL Oracle pass through - passing null values in date fields

Re: PROC SQL Oracle pass through - passing null values in date fields

PROC SQL Oracle pass through - passing null values in date fields

Re: %str for multiple values Error