About Kirtid

Kirtid · ‎06-17-2021

Thanks again for the response. I tried to frame a sample test data as below. There can be a case where date fields can be also used for grouping, as these are dynamic variables. Input - Dataset b; Input id name$ sale1 sale2 sale3 date1; Cards; 1 aa 10 20 30 1 aa 5 7 10 2 bb 28 60 6 2 cc 10 5 5 2 cc 6 6 6 ; Run; Field to which aggregate function needs to be mapped comes from another table as below.From this table the macro variables &aggfunc and &grouplist are created. Field Func ID group Name group Sale1 min Sale2 min Sale3 sum %put &aggfunc; ID,Name,Min(sale1) as sale1, min(sale2) as sale2, min(sale3) as sale3 %put &grouplist; ID,Name Proc sql; Create table as sum_out as Select &aggfunc., &grouplist. From B Group by &grouplist; Quit; Output dataset result - sum_out 1 aa 5 7 40 2 bb 28 60 6 2 cc 6 5 11

Kirtid · ‎06-17-2021

Hello Reeza, Thanks for your reply. Sorry I have not tried it with proc means.. these 1600 fields are dynamic and I get them as macro variables so my current proc sql will look like as below - Prod sql ; Create table sum_out as Select &aggfunction, &grouplist from B group by &grouplist; Quit; &grouplist - the fields on which the grouping should be done &aggfunction - fields on which aggregation happens, falling in a group. Here the aggregate function can be MIN, Avg, Max, sum etc

Kirtid · ‎06-17-2021

I have a dataset- B which as 50000 records and around 1600 columns. I try to summerize this dataset B in such a way that the minimum value of all numeric field should be displayed in the output for every group. This dataset has only two datatypes - Number and Character, for all numeric field MIN function is applied and all Character fields will be grouped here.. This aggregation is done on all 1600 columns. With Proc sql, the performance is bit slow, could you please assist if there is an alternate approach that will have a better performance than using proc sql step here?. Ex: out of 1600 fields- 1000 fields are numeric and 600 fields are character, then grouping is done on 600 fields and for every numeric field following in that group, the minimum value should be output in that group.

Kirtid · ‎10-02-2020

Seriously!!.. I spent half my day on this.. Thanks much it worked.

Kirtid · ‎10-02-2020

Hi, Im using SAS DI and seem to have a Problem in reading a date value from Excel to SAS. the read Excel date value is in character and when i use the put statement to convert to numeric and Format it with a date9. in SAS DI i get the below error message. Numeric format F in PUT function requires a numeric argument. ERROR: Numeric format F in PUT function requires a numeric argument. I see this Problem seem to occur only in proc SQL but not in a datastep. Could you please advise as to how i can make this work in proc SQL. Below code snipet is added for reference. Thanks in advance. data A; format c date9.; b=' 21273'; c=put(strip(b),10.); run; proc sql; select put(strip(b),10.) format = date9. from A; quit;

Kirtid · ‎06-28-2020

Thanks for the solution. I have a question here, the solution worked for the provided example, but in original I have around 10 columns(X1... X10) for which this valid check to be performed, in such case using 'union corr all' would it be fine while creating the invalid dataset?, wondering if there will be any performance issues, kindly advise.

Kirtid · ‎06-28-2020

Thank you all much for your time and for giving me a different view on the solution approach, it's really interesting for me to note them. Have a great day..

Kirtid · ‎06-27-2020

Hi, I have the below case where I'm unable to find a best approach to implement the solution, could you please advise me here. Input data: (in the below example I have the item and year columns grouped, and based on the values of X1 and X2 , valid and error datasets are created. Item Year X1 X2 A 2018 12 A 2018 10 B 2019 20 B 2019 5 C 2017 1 C 2017 1 2 C 2017 D 2016 E 2016 10 2 E 2016 6 Aggregation Criteria in which the two output datasets are created as below: Valid dataset will always have a unique entry from the group. The non-null values of X1 and X2 in a group is considered to be valid(ex - Item : A) when X1/X2 have same values in a group, then the unique value is picked and added to the valid dataset (ex- item :C) Conflicts in values of X1/X2 in a group are considered to be an error. when X1/X2 values are different in a group then the last value of X1/X2 in the group are picked as a valid. So in this case the valid dataset will have a unique entry for the mentioned group (ex- Item: B ) and the error dataset will save the both the records of B from the input dataset. When there is only in entry in the input, then it goes to the valid dataset (ex - Item: D) Output1 - Valid dataset: Item Year X1 X2 A 2018 10 12 B 2019 5 C 2017 1 2 D 2016 E 2016 6 2 Output2 : Error dataset Item Year X1 X2 B 2019 20 B 2019 5 E 2016 10 2 E 2016 6

Kirtid · ‎05-13-2020

Sorry, the paths that I mentioned was only a dummy path I framed to show an example(corrected them as below). When I define libname directly as below , they work perfectly fine, but problem comes only when I want to refer the libname by a macro variable. rsubmit; libname wlib "c:\test\myfiles" ; endrsubmit;

Kirtid · ‎05-13-2020

Thanks for the response again. I used %SYSLPUT too but still the libname for the windows server is not recognized and getting the same macro resolving failed error :(. I still wonder as to why it is not getting resolved.

Kirtid · ‎05-13-2020

Thanks for the response. I can't mention the libname statement directly in the macro without passing it through %str, as my process would comment out all the libname at the end, so that's why I want to mask the libname. The resolving of libname fails online the below case, since the %str does not work when called inside the macro %upload. %macro host_check; %if %sysfuncn(libref(hostlib)) = 0 %then %upload; %mend host_check; %macro upload; rsubmit; %let libws = %str(libname wlib "test/path/on/windows"); &libws; proc upload inlib=hostlib outlib=wlib; select <dataset>; quit; endrsubmit; signoff; Error message: " &libws - Apparent symbolic reference libws is not resolved Error 180-322 Statement is not valid or it is used out of proper order. " Kindly assist.

Kirtid · ‎05-13-2020

Hello, I want to transfer a dataset from host to windows server. I use a customized macro that creates a connection between the host and the windows server. The problem is that I define the windows server path via a macro variable as below - %let test = %str(Libname librer "test/path/on/windows"); &test; I have some validations to be done on host server prior connecting to windows, so I will have to pass the above lines in macro call, but since I pass the libname reference using %str, I get an error that the lib name can't be resolved. Could someone please assist me here. Thanks in advance. KK

Kirtid · ‎03-30-2019

Thanks all for the giving me different views on this topic. Another approach I used tranwrd func with cat . Month in strip(Cat ("'",tranwrd(&mon.,",","','"),"'"))

Kirtid · ‎03-26-2019

Thanks for your reply.. hmm, but the problem is that the macro values are separated by a comma as these values are to be be used for different other purposes, so I may not be able to adjust or edit the way they appear and I just brought this month only as a sample case. if my macro values are still appearing with comma separation, how can we still make it work ?

Kirtid · ‎03-26-2019

Thanks for your reply Reeza. The macro values are created from another dataset from where we get the unique month information. Would it work by having single quotes around the values? - something like this : %let mon = 'jan','feb';

Online Status	Offline
Date Last Visited	‎09-21-2022 07:23 PM

Re: Proc Sql performance issue

Re: Proc Sql performance issue

Proc Sql performance issue

Re: Excel date to SAS date format issue

Excel date to SAS date format issue

Re: Help with code approach

Re: Help with code approach

Help with code approach

Re: %str masking problem in a macro

Re: %str masking problem in a macro

Re: Proc Sql performance issue

Re: Proc Sql performance issue

Re: Proc Sql performance issue

Re: Proc Sql performance issue

Re: Proc Sql performance issue

Re: Proc Sql performance issue

Re: Proc Sql performance issue

Proc Sql performance issue

Re: Excel date to SAS date format issue

Excel date to SAS date format issue

Re: Help with code approach

Re: Help with code approach

Help with code approach

Re: %str masking problem in a macro

Re: %str masking problem in a macro

Re: %str masking problem in a macro

%str masking problem in a macro

Re: multiple Macro values issue - better way to apply filter on these ...

Re: multiple Macro values issue - better way to apply filter on these ...

Re: multiple Macro values issue - better way to apply filter on these ...