CharlotteCain Tracker

Re: creating new columns from a column

CharlotteCain — Tue, 13 Nov 2018 18:18:41 GMT

@lalohg Not appropriate to mark your own response. You should choose one of the two solutions you got and give them the credit!

Re: Creating group data

CharlotteCain — Tue, 02 Oct 2018 17:18:35 GMT

The put function solution has been suggested twice in the same thread by @novinosrin and @Karen_Horton. Doesn't really help or make much difference in duplicating the same solution. For the benefit of wider community, I request to you kindly offer new alternatives rather. My 2 cents & Thank you!

Re: Select (conditional) latest date using array

CharlotteCain — Mon, 27 Aug 2018 17:12:25 GMT

Great solution. Super fast performance!!!

Re: I need explanation for this code and the macro

CharlotteCain — Mon, 09 Jul 2018 19:20:34 GMT

I would recommend reading this book

https://www.sas.com/store/books/categories/usage-and-reference/carpenter-s-complete-guide-to-the-sas-macro-language-third-edition/prodBK_67815_en.html

And then I bet your understanding would be spot on!

Re: BASE SAS Logic help..

CharlotteCain — Fri, 06 Apr 2018 00:10:59 GMT

All that mark was asking is to show what you have tried and get help to fix or modify your solution. That's all and it didn't sound rude at all. I have been told that too in the past and more in a rude manner by many but i took that as an affirmative. And yes, many users do get here taking advantage of other's time. Unfortunately that happens all the time.

help request in understanding indirect macro variable references?

CharlotteCain — Tue, 05 Dec 2017 16:56:30 GMT

help request in indirect macro variable references?

%let city1=london;

%let city2=frankfurt

%let city3=paris;

%let n=1;

Please correct my understnding:

&&city&n would resolves to &city1--> this resolves to london. Ok.

Looping the n one by one will get the above city names. Fine

so I suppose && resolves to &

What baffles me is use of more than && ampersands and resolving many other vars with multiple ampersands(&&&&) I vaguely remember reading an example on SAS documentation something dog resolves to cat and cat resolves to monkey or something like that. Should any experts lend their time in helping me understand this concept, I will most appreciate it.

Regards,

Charlotte

Re: How to update a teradata table using SAS access?

CharlotteCain — Tue, 04 Apr 2017 17:39:05 GMT

Thank you Xia, I hope you are keeping well.

OR you could try PASS-THROUGH sql, but you need to know TD 's sql.

proc sql;

connect to teradata.......

execute (......)

by teradata

That's the understanding I needed. A TD sql in the execute did the job. I really appreciate it. Take care!

Re: How to update a teradata table using SAS access?

CharlotteCain — Tue, 04 Apr 2017 02:55:45 GMT

I didn't try MLOAD. I have been trying UPDATE statement yes. I wonder whether it is possible to execute a teradata query using proc sql access. If yes, I just don't know how. I am unable to find some examples online.

How to update a teradata table using SAS access?

CharlotteCain — Mon, 03 Apr 2017 15:59:40 GMT

How to update a teradata table using SAS access?

I have two teradata tables:

Table 1:

Id Col1 Col2

1 x y

1 a b

2 x y

3 g h

4 x y

4 a b

Table 2:

ID Remark Date

1 complete 02jan2004

1 pending 06jan2004

2 complete 08jan2005

3 pending 08feb2005

3 pending 07mar2005

3 pending 09mar2005

I neeed the remark column with the max of date to be updated to table 1. Suppose if those were sas datasets, the code is pretty easy:

proc sql;

select a.*, b.remark

from table1 a

left join

(select distinct id, remark, max(date) as max_date from table2

group by id;

having date=max_date) b

on a.id=b.id;

quit;

But I my tables are teradate tables, I find the SAS access execute statement pretty challenging. Can anybody help?

Re: any programmatic way to compute sets/probabilities the problem perhaps with a SAS function?

CharlotteCain — Mon, 17 Oct 2016 15:49:51 GMT

Good evening Genius @Ksharp and @PGStats Thank you so much for the help. Well, I honestly didn't realise this could have posted in IML forum. Both, Amazingly elegant approach and neat. PG Sir, I wanted in a new variable just for reading ease and your understaing was right.

Xia, Sorry for the late response as I am aware by the time I get home from work and attending afternoon college, you must be sleeping in China. I will study your code over night, understand and will follow up with you if i don't understand some parts. And I trust I can bother PG while you are asleep if he stays warm enough in freezing Canada

As always I can't thank you enough,

Regards from England,

Charlotte

Btw, Can you share me a link to the detailed documentation of IML

any programmatic way to compute sets/probabilities the problem perhaps with a SAS function?

CharlotteCain — Sun, 16 Oct 2016 10:11:54 GMT

Hello all, any programmatic way to compute the problem perhaps with a SAS function?

Let x be the set of possible outcomes from rolling a dice twice, x = {(a, b) : a, b ∈ {1, 2, 3, 4, 5, 6}}.

Let A be the subset of x given by {(a, b) ∈ x : a + b ≥ 10} and let B be the subset {(a, b) ∈ x : a = 5}.

(a) Write the sets A and B in a new variable

(b) Write A and B in list form (i.e. as a list of elements enclosed in {. . .}).

(d) write the set x \ A in in a new var.

Re: A Macro question on a concept that I would like to understand, Please?

CharlotteCain — Sat, 07 May 2016 23:54:09 GMT

Thank you @Quentin @Astounding @FreelanceReinh for making me clearly understand the concept and sorry for the bother on a saturday(haha). Well, macro programming is very intriguing as it seems to me a layer on top the SAS codes that generates extra texts, resolution of macro references, and the passage of macro compilation/compilation to SAS compiler is a beauty tale. I am loving it. Thanks once again!

Regards,

Charlotte

PS There is another concept(indirect references) that makes my brain crazy. I will post that as a new question later

A Macro question on a concept that I would like to understand, Please?

CharlotteCain — Sat, 07 May 2016 09:52:58 GMT

I would like to understand how the macro processor looks up the global symbol table when resolving the second %put outside statement. If my understanding is correct, the first %let assigns a value of 1 in the global symbol table, however when forcing the same name as a local within a macro definition, what really happens? Does it create another macro variable with the same name in the local symbol table when a macro variable with the same name exists in the global symbol table? Any chance of overlap or ambiguity here? I would appreciate if somebody can explain the mechanism at length, please and thanks!!!!

Cheers,

Charlotte from England

%let variable=1;
%macro routine;
   %put ***** Beginning ROUTINE *****;
   %local variable;
   %let variable=2;
   %put The value of variable inside ROUTINE is &variable;
   %put ***** Ending ROUTINE *****;
%mend routine;
%routine
%put The value of variable outside ROUTINE is &variable;

Submitting these statements writes these lines to the SAS log:

***** Beginning ROUTINE *****
The value of variable inside ROUTINE is 2
***** Ending ROUTINE *****
The value of variable outside ROUTINE is 1

Re: Requesting efficient solution to compare by groups look ahead values?

CharlotteCain — Fri, 05 Feb 2016 07:30:55 GMT

@Ksharp Yes Xia, I know you would be gone for Chinese new year holidays to your home town right? I remember you did the same last year 🙂 . I was anyway going to wish you by sending you a peronal greetings email to you, but anyway since you mentioned that I wish you a fantastic chinese new year that brings hope, joy, happiness and peace. Thank you for everything and enjoy your break!

I'll be anxiously waiting for your return. Take care and stay safe!,

Charlotte

Re: Requesting efficient solution to compare by groups look ahead values?

CharlotteCain — Fri, 05 Feb 2016 05:02:28 GMT

Thank you Xia, You are too too too Brilliant!!. The SAS world envy you and in many cases I have noticed here, if nobody can solve a problem,only Xia Keshan can. Many people have said this and I say this too many times. You are blessed and gifted talent and thank you for being a true dear friend. You have helped me so much

Re: Requesting efficient solution to compare by groups look ahead values?

CharlotteCain — Thu, 04 Feb 2016 04:16:15 GMT

@Ksharp Good morning Xia, It's very nice to just wake up to your solution as always. I am really sorry for using too many words and please accept my apology for that.

Yes, the data is sorted by store_id product_id and monthly_period(sorted by date and then i took month of date using month function) and the result should be in the order store_id monthy_period once the comparison is done for product_win_point variables.

Thank you as always, and I hope you are well?

Regards,

Charlotte

Requesting efficient solution to compare by groups look ahead values?

CharlotteCain — Wed, 03 Feb 2016 18:30:46 GMT

Dear SAS Folks, I request your help for an efficient solution for a very large dataset that challenges to compare to By-groups within by groups.

Here is the sample of my input data-set(have) with 5 columns:
STORE_ID PRODUCT_ID PRODUCT_WIN_POINT1 PRODUCT_WIN_POINT2 MONTHLY_PERIOD
W169 F1 1 0 jan
W169 F1 0 1 feb
W169 F1 0 0 mar
W169 F1 1 1 apr
W169 F1 0 0 may
W169 F1 1 1 jun
W169 V1 0 1 jan
W169 V1 1 1 feb
W169 V1 1 0 mar
W169 V1 1 0 apr
W169 V1 0 1 may
W169 V1 0 1 Jun
W169 T1 0 1 jan
W169 T1 1 0 feb
W169 R1 0 1 mar
W169 R1 0 0 may
W169 R1 0 1 jun
B291 B3 1 0 jan
B291 B2 1 1 jun
B291 B44 0 1 july

The need is to compare PRODUCT_ID values against MONTHLY_PERIOD within STORE_ID values.
Details:
Point1: For each STORE_ID, I need to compare PRODUCT_ID F1(product_win_point1 value) to the V1(product_win_point1 value) to the T1(product_win_point1 value) and similarly across all product_id's within a store_id that has values for the month of Jan. In the example above, you would notice there is no product_win_point1 and product_win_point2 value of R1 for the month of Jan.

Point2: Likewise, feb (product_win_point1) value of product id's needs to be compared as explained in point1. And the same exercise should be done for all months available, that is jan to jan, feb to feb, mar to mar........

Point3: Comparison logic--> For example, When comparing the values of product_win_point1 across months, let's say taking the example above:
For store_id W169,for the month of Jan, the product_win_point1 value for product_id F1=1, V1=0, T1=0,R1 is missing. Since there is at-least one product_win_point1 with value 1, the result should be stored as 1 for the month Jan. Likewise the same rule applies when comparing product_win_point1 values of product id's for feb against feb, mar against mar......

Point4: The same comparison logic as mentioned in Point1,2 & 3 has to be applied for PRODUCT_WIN_POINT2 values of product_ids against monthly periods.

I trust the points explained above can be comprehended and if not I am so keen to detail further whatever i can.

So, the final resulting WANT(Output) dataset:

WANT:
STORE_ID MONTHLY_PERIOD MONTH_as_number COMPARED_PRODUCT_WIN_POINT1 COMPARED_PRODUCT_WIN_POINT2
W169 jan 1 1 1
W169 feb 2 1 1
W169 mar 3 1 1
W169 apr 4 1 1
W169 may 5 0 1
W169 jun 6 1 1
B291 jan 1 1 0
B291 jun 6 1 1
B291 july 7 0 1

Re: Date Conversion

CharlotteCain — Mon, 28 Dec 2015 18:19:36 GMT

Hi Bob, May i request you to please enlighten me on the differences between applying $w. format and just like 8. format without the dollar sign. Also, does applying an x amount of width caters to values less than the number of bytes in the value?

This confuses me big time:(

Sorry for the bother and merry christmas and happy new year to you,

Charlotte

How to the solve the look solution of this problem with very very large dataset?

CharlotteCain — Tue, 24 Nov 2015 20:02:52 GMT

How to the solve the look solution of this problem with very very large dataset?

I have a two datasets, dataset1 and dataset2.

dataset1 has

client_id var1_var25 (so in total 26 variables)/8client_id is the key and is unique but not ordered */

dataset2 has duplicate client_ids/* obviously coz the dataset has daily balances of bank customers for the last so many months*/

client_id date daily_balances

1 14/10/2014 5000 /*some date and daily balances of clients until end of file*/

Q1. I need to simply to do a left join to get the daily balances in dataset1 coz i want the daily balances of only those client_id's of dataset1? The problem is that that dataset2 has over 250 million records and dataset1 has a million records. Any efficient solution with SAS 9.2 please?

Q2. I want the last balance of each client_id, just as simple as to sort and last.id after the join?

I have no clue how to manage the look up dataset1(with 1 million records) to dataset2(with 250 million records). I'd appreciate any help please.

Re: Request for Efficent look up from one to many datasets?

CharlotteCain — Wed, 14 Oct 2015 18:59:33 GMT

@MikeZdeb Thank you so much Sir! for adding the comments. I really really appreciate. Sorry for the bother. Have a great day!