About nickspencer

nickspencer · ‎12-13-2019

@SASKiwi I have multiple variables to change. Is there a way to achieve this at a time for multiple variables ?

nickspencer · ‎12-13-2019

I have a dataset with multiple columns with name starting ind_ (eg ind_name, ind_adr,ind_sal) and the values are either 1 or 0 . I will need to convert those values to Y or N with the variable names keeping same. I tried using proc format and the columns showed correct values of Y or N but they were numeric. Is there a way I can achieve that by also changing the data type to character? Thanks in advance

nickspencer · ‎12-12-2019

@Tom I don’t have privilege to create permanent table so I am trying to create volatile table and load data into it. I know fastload and multiliad won’t work with volatile table so trying to use multistmt but I am getting an error

nickspencer · ‎12-12-2019

I am trying to load SAS dataset into a teradata table but I am getting error while using multistmt option with proc append. My program looks like below. Libname tdwork teradata user=“xxxxx” password=“xxxxx” tdpid =“xxxx” connection=global dbmstemp=yes; proc sql; connect to teradata (user=“xxxxxx” password=“xxxxxx” tdpid=“xxxxx” connection =global); execute ( create multiset volatile table test_tera (column1 char(40)) no primary index on commit preserve rows) by teradata; execute (commit work) by teradata; quit; proc append base=tdwork.test_tera(multistmt=yes) data =dataset1; run; ERROR: Teradata connection: object ‘test_tera’ does not exist. Correct error and restart as an append process with option TPT_RESTART=YES. Since no checkpoints were taken, if previous run used FIRSTOBS=n, use the same value in the restart. Am I doing something incorrectly? When I remove multistmt option, it is running forever. I thought multistmt would work with volatile table. Any help is appreciated Thanks

nickspencer · ‎11-18-2019

@FreelanceReinhard & @ballardw This makes sense. One of the value is coming from a teradata table and has decimal(18,2) format. I like your idea of rounding off. Does it possess any risk with except operator and comparing with existing dataset value? Or converting it to character using cats() function while using the except operator should work ? Which solution will be more efficient ?

nickspencer · ‎11-18-2019

Hi ALL, I have a proc sql step which looks like below. proc sql; create table test as select compress(key) as key, total_amt , a_cd, b_cd from table1 except select compress(key) as key, total_amt , a_cd, b_cd from table2; quit; I have two tables table1 and table2. I am getting weird result. table1 key total_amt a_cd b_cd 9528553591287 209.20 9707589975114 0.00 table2 key total_amt a_cd b_cd 9528553591287 209.20 9707589975114 0.00 test key total_amt a_cd b_cd 9528553591287 209.20 I believe I should have got 0 records with the above query but I am getting some rows although they have same values in table 1 and 2. The formats/lengths of the columns are different in two tables but I don't believe that is the cause of the issue. table1: total_amt format: 20.2 informat 20.2 numeric length 8 a_cd format: $1 informat $1 b_cd format: $4 informat $4 table2: total_amt format: 21.2 informat 21.2 numeric length 8 a_cd format: $1 informat $1 b_cd format: $10 informat $10 Any idea why ,although the data values are same in both tables, I am getting results with the except operator? Thanks in advance Nick

nickspencer · ‎11-14-2019

@Astounding I don’t think I understand when you say change other macro to create dup_list. Do you mean having the %global dup_list outside this check duplicate macro?

nickspencer · ‎11-14-2019

Hi all, I have the below portion of the code which works perfectly fine but when I put the whole code inside another macro it fails. The section of the code looks like this. %macro check_duplicate; %global dup_list; if &dup_list= %then %do; %let dup_list=‘99999’; %end; %mend check_duplicate; %check_duplicate; This above section was working fine on my code but then I had to make some changes and put the whole code inside another macro and I am getting this error. ERROR: Attempt to %GLOBAL a name (DUP_LIST) which exists in a local environment. How can I handle this error without making much changes to the structure of the code. Any suggestions or ideas will be highly appreciated. Thanks in advance. Nick

nickspencer · ‎06-14-2019

I have a SAS dataset with a column main_col that might have values of any length. I need to split the main_col into multiple columns col1-col10. Each column should be of length $500. What can be the best way to do that? Thanks

nickspencer · ‎06-13-2019

I am running my code in Linux server and creating an excel file in a directory in the server. But I need to send this file or create this excel file in the shared folder in windows. Is it possible to do so from Linux server? Any suggestions will be highly appreciated. Thanks,

nickspencer · ‎03-28-2019

Hi, I have a SAS dataset that has a column AMT which is a numeric variable and has format 15.2 . I want to convert it to a character variable and also add a dollar sign. I did final_amt=put(AMT,dollar15.2) and it worked but is it the right way to do? Or is there any other correct way to do? I always thought the informat is to read the variable rather than to display but I am confused I am able to display in dollar format with informat. Any suggestions or insight is appreciated. Thanks

nickspencer · ‎03-08-2019

I am trying to load this data into a teradata table but for some reason it has the column as numeric

nickspencer · ‎03-08-2019

Hi, I am trying to convert a character variable to numeric but when I use input function it is returning me the numeric value in scientific notation. Is there a way I can get a proper value ? I am using the input function as below acct_id=input(accountid, 20.); I tried the best32. informat. It still returns in the same format with scientific notation like 4.9975044E14 The original format/informat of the accountid shows $15. Any help that will help me get the proper value will be appreciated. Thanks

nickspencer · ‎10-30-2018

I have a sas code which writes into a teradata table. I have been running this code for a year now and was working perfectly fine. I haven’t made any changes to the code but since last two days it is giving me the error below. ERROR: Teradata connection: CLI error 207 (no message text available) Correct error and restart as an APPEND process with option TPT_RESTART= YES. Since no checkpoints were taken, if the previous run used FIRSTOBS=n, uae the same value in the restart. ERROR: ROLLBACK issued due to errors for data set mydata.dsn.data. Code that inserts the data: Proc sql; Insert into mydata.dsn (multiload=yes tpt_restart=yes) select a, b, c from abc; quit; Is anyone familiar with this error message? The weird part is it was working fine and suddenly it is erroring out and nothing is updated in the code. I am not sure if something changed in the Sas server or the database. Any ideas or suggestions is highly apreciated. Thanks in advance.

nickspencer · ‎09-16-2018

Hi, I have the below dataset with multiple customer ids in numeric format. I need to convert them to character variables with leading zeros with 11 character length. How can I do that in a single step for multiple variables? data have; input date date9. cust_id_1 cust_id_2 cust_id_3 cust_id_4 cust_id_5 cust_id_6; datalines; 01SEP2018 1458976 768976 9856786 7634543 494756 6578436 10SEP2018 859034 7689543 214987 7639856 5674849 35467869 11SEP2018 1254679 1615432 165908 6752143 190876 1098723 ; RUN; The final dataset should look as below. date cust_id_1 cust_id_2 cust_id_3 cust_id_4 cust_id_5 cust_id_6 01SEP2018 00001458976 00000768976 00009856786 00007634543 00000494756 00006578436 10SEP2018 00000859034 00007689543 00000214987 00007639856 00005674849 00035467869 11SEP2018 00001254679 00001615432 00000165908 00006752143 00000190876 00001098723 Thanks.

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

Re: replace 1/0 with Y/N

replace 1/0 with Y/N

Re: Error loading data in teradata volatile table

Error loading data in teradata volatile table

Re: Issue with except operator in proc sql

Issue with except operator in proc sql

Re: %global macro issue

%global macro issue

Split the values in different columns

Creating an excel file in shared folder

Converting numeric variable to character and add dollar sign

Re: Converting character to numeric value

Converting character to numeric value

SAS Teradata Error

convert multiple numeric variables to character with leading zeros