About nickspencer

nickspencer · ‎11-28-2023

Hi All, I have a code which is writing some data in the log as a part of note/warning. I want to suppress writing the column names and their values in the log. Is there any option that will prevent writing column names and their values? I am fine with the note though. Note: Invalid numeric data at line 520 column 35 column1=abc Column2=500 column3=12345……… Thanks in advance Nick

nickspencer · ‎09-06-2023

Hello All, I have a SAS dataset with account numbers(character). I want to display only last 4 masking the remaining with ‘x’. Input: Account 123456 34567898 Output: Account xx3456 xxxx7898 Any suggestions will be highly appreciated.

nickspencer · ‎09-13-2022

Hello everyone, I have a proc sql code that is taking more than an hour hour to complete. Is there a way I can optimize it to run quicker? Code: Proc sql; Create table want as Select distinct a.*, c.*, b.column1 From table1 a Left join table2 b On a.ID1=b.ID1 Left join table3 c On a.ID2=c.ID2 ; Quit; Any suggestions is highly appreciated. Thanks, Nick

nickspencer · ‎04-09-2022

Hi all, I have a sas dataset with around 15k record that I have to do an inner join with an Oracle table. I am doing a heterogeneous join but it is taking extremely long to complete even with options like readbuff, dbkey and multi_datasrc_opt=in. Is there any other way I could run my program faster? Thanks, Nick

nickspencer · ‎02-21-2022

Hello everyone, I am trying to translate the following code from data step to sql. How do I write the following code in SQL? proc sort data=dsn; by ID name; run; data final; Set dsn; by ID Name; Class+1; if first.ID then class=1; run; Appreciate any help. Thanks, Nick

nickspencer · ‎07-23-2021

Hello friends, I am trying to do my development work on Sas studio on Viya server and stop using enterprise guide. But I am struggling to save the .sas program files on my local machine. Is there a way I can save the program files on my local machine and open/edit/run on sas studio whenever I need? Thank you, Nick

nickspencer · ‎04-09-2021

Hello everyone, Is it correct to use date constant in %let statement? I will be using this value only to filter the data in a datastep later on. %let mydate=‘01jan2021’d; data test1; set test ; If dt=&mydate; Run; Please let me know your thoughts. Thanks, Nick

nickspencer · ‎03-29-2021

Hello all, I have a dateset Have. It has multiple records by identifier ID, all the values are same except for attrib1 and attrib2. I want to change the attrib 1 values to column names and assign them the values of attrib2. Have: ID Name State Date attrib1 attrib2 123 John AZ 1/1/2021 level A 123 John AZ 1/2/2021 Score 120 123 John AZ 1/3/2021 Maint1 202 123 John AZ 1/4/2021 Maint3 501 123 John AZ 1/5/2021 Maint2 302 123 John AZ 1/6/2021 Maint4 101 234 Mary CA 2/6/2021 Maint2 150 234 Mary CA 2/7/2021 Maint1 101 Want: ID Name State Date level Maint1 Maint2 Maint3 Maint4 Score 123 John AZ 1/1/2021 A 202 302 501 101 120 234 Mary CA 2/6/2021 101 150 What will be the best way to achieve this? Any suggestions or solution is appreciated. Thanks, Nick

nickspencer · ‎02-12-2021

@Tom this works great except that I have number of other columns in table1 to group by along with state column(may have different value). Is there a way to handle that?

nickspencer · ‎02-12-2021

@mkeintz this is great and there is only one patiend_id/comp_id. But is there a way to achieve the same in proc sql?

nickspencer · ‎02-12-2021

Hi all, I am trying to create an indicator on table1 based on information on table2. Table1 Patient_id comp_id date 123 A111 1/2/2021 234 A124 1/3/2021 567 A234 1/4/2021 Table2 Patient_id Comp_id Date State 123 A111 1/5/2020 AZ 123 A111 1/4/2020 AZ 123 A111 2/5/2021 PA 234 A124 1/3/2021 NY So, I want to create a column called indicator in table1 and gave indicator value of 1 if the same patient and company is present in table2 and date in table1 is less than any date for that condition in table2 and state is AZ. That means only first row in table1 will have indicator of 1. I tried few ways but got duplicate in some cases. What may be the best way to achieve this with proc sql?

nickspencer · ‎01-04-2021

I am trying to bring in latest record from teradata based on two IDs, but without grouping them. Both these IDs have been reassigned to each other so I want to pick up the latest. I do not want to have duplicates by any IDs. Have: ID1 ID2 date 123 987 12/01/2020 123 654 05/10/2020 345 999 01/20/2020 456 999 12/20/2020 231 789 12/10/2020 231 789 12/21/2020 Want: ID1 ID2 123 987 456 999 231 789 Since the data is in teradata, might be efficient to use sql/teradata sql instead of data step? Please advice.

nickspencer · ‎11-12-2020

@Kurt_Bremser I does not have to be a teradata table. I need the output as a sas dataset.

nickspencer · ‎11-12-2020

Chrisnz I missed the join condition on the query. Here is the complete query. Update a From master a, table2 b set x=1,y=1 where a.ID=b.ID and a.status=‘Inactive’ and b.status2=‘Active’

nickspencer · ‎11-12-2020

Hello all, I have a master dataset with few hundred columns including columns ID, x ,y and status. I have another dataset table2 that also has columns ID and status2. I want to update the column values of x and y on master dataset based on criteria that involves both master and table2 datasets. I am doing this in teradata sql right now. Update a From master a, table2 b set x=1,y=1 where a.status=‘Inactive’ and b.status2=‘Active’ Can I do the same update with sas datasets?

Online Status	Offline
Date Last Visited	‎11-29-2023 01:34 AM

Remove data from the log

Replace all characters with x except last 4 four

Proc sql left join

Heterogenous join of sas dataset with oracle

Translate data step to sql

Save sas program file on local machine

Date constant in macro

change the values to variable names

Re: Proc sql left join no duplicates

Re: Proc sql left join no duplicates

Re: Issue with except operator in proc sql

Re: Issue with except operator in proc sql

Re: Concatenate if duplicate

Re: Concatenate if duplicate

Re: Concatenate if duplicate

Translate data step to sql

Save sas program file on local machine

Remove data from the log

Replace all characters with x except last 4 four

Proc sql left join

Heterogenous join of sas dataset with oracle

Translate data step to sql

Save sas program file on local machine

Date constant in macro

change the values to variable names

Re: Proc sql left join no duplicates

Re: Proc sql left join no duplicates

Proc sql left join no duplicates

bring in latest record from teradata

Re: Update dataset columns based on another dataset

Re: Update dataset columns based on another dataset

Update dataset columns based on another dataset