About LearnByMistk

LearnByMistk · ‎05-24-2016

i wrote this query using alias var names and yes sql in questions isnt correct it missed end statment . But thanks both of you for revert.

LearnByMistk · ‎05-24-2016

Hi All, I have ID currdate pvecnt nvecnt 1 11apr2016 0 2 1 12 apr 2016 3 4 1 13apr2016 5 5 1 16apr2016 6 10 i need 2 vars sum(pve/nve) cnt on weekend and sum(pve/nve) cnt on weekday logic i used proc sql; select id ,(case when pvecnt >0 and nvecnt>0 and weekday(currdate) in (1,7) then sum(pvecnt,nvecnt) as weekend_cnt, case when pvecnt >0 and nvecnt >0 and weekday(currdate) not in (1,7) then sum(pvecnt,nvecnt) as weekday_cnt) from table group by id; quit; rather than getting 1 result m getting 4 rows and sum of their vars in horizontally what i want : id wknd_cnt wkdy_Cnt 1 16 17 i'm not considering row where either of pvecnt or nvecnt is 0. Thanks

LearnByMistk · ‎03-03-2016

Hey thanks for reverting ..my apology to reply late. We can get the same results using case when but i dnt know how to exit code once my condition fulfill . as Have : data have; length yyyymm $6; input subscriber_num yyyymm id_flg; cards; 101 201512 1 101 201511 23 101 201510 223 102 201512 . 102 201511 1 102 201501 54 103 201512 . 103 201511 . 103 201510 1 ; run; Want subscriber var_m1_m4 101 1 102 2 103 3 what i dont need is subscriber var_m1_m4 101 1 101 2 101 3 @Kurt_Bremser wrote: Slightly updated code (after getting some example data): data have; length yyyymm $6; input subscriber_num yyyymm id_flg; cards; 101 201512 1 102 201512 . 102 201511 1 103 201512 . 103 201511 . 103 201510 1 ; run; data want (keep=subscriber_num var_m1_m4); set have; if id_flg > 0 then do; var_m1_m4 = intck('month',input(yyyymm!!'01',yymmdd8.),'30jan2016'd); if 1 <= var_m1_m4 <= 4 then output; end; run; renders this: 101 1 102 2 103 3 102 2 103 3

LearnByMistk · ‎02-26-2016

nope that's not correct sir, kindly look at the example

LearnByMistk · ‎02-26-2016

subscriber_num yyyymm id_flg 101 201512 1 102 201512 . 102 201511 1 103 201512 . 103 201511 . 103 201510 1 ouptut subscr var_m1_m4 101 1 102 2 103 3 consider values for month 201512 will have var_m1_m4 as 1 values in 201511 will be 2 and so on

LearnByMistk · ‎02-26-2016

logic is something like case when yyyymm=201512 and id_flg >0 then 1 case when yyyymm=201511and id_flg >0 then 2 case when yyyymm=201510 and id_flg >0 then 3 case when yyyymm=201509 and id_flg >0 then 4 else . end as var_m1_m4. In case if i have id_flg value across each month i get var_m1_m4 as 4 . Output required: if id_flg gt 0 for 201512 then var_m1_m4 =1 and system skip further processing. in case if 1st condition doesn't satisfy than it should go and look for next step. Thanks in advance.

LearnByMistk · ‎06-16-2015

Hi , Which approach is more suitable in extracting and processing data which is there in Teradata is this libname teralib teradata server=server user=id pwd=password; or proc sql; connect to teradata(server=server user=id pwd=password); create table abc as select * from connection to teradata (select * from employees where abcid=2); quit; Scenario : I 'm extracting a huge TD table and want to do few aggregation on data but it throws me spool space error . Fast response is really appreciable . Thanks.!!

LearnByMistk · ‎05-20-2015

Thanks all of you for your help * Support ..that's the only reason i love this community

LearnByMistk · ‎05-20-2015

Ok let me check on my given data than i revert but really thanks for so prompt reply ..

LearnByMistk · ‎05-20-2015

Hi , i need to figure out few steps with in a same or 2 queries data i have drp_cnt blk_cnt cell site 23 43 A 132 34 B 154 63 C So i want to figure out average of all drop_cnt as (103) and blk_cnt as (46.67) .. this average is not depending on cell site but a genral average of whole count (drop n block). data want drp_cnt blk_cnt cell site drp_flg blk_flg 23 43 A 1 1 132 34 B 0 1 154 63 C 0 0 Here drp_flg and blk_flg ...i create depending upon the average values of drop n blk count ie drop_cnt as (103) and blk_cnt as (46.67)...so if value is > avg its 0 else 1 . I tried to resolve this using calculated staement in proc sql but dont get the required result . Your help is greatly appreciated .

LearnByMistk · ‎05-06-2015

Yes RW9@ & KurtBremser... i agree there are multiple ways but couldn't bring them that time so used that.. actually i used earlier select a,b from (select id ,cat from T 1 where some condition) aliasname....as i forgot to put this alias name so it makes me to put question here on discussion portal ...but thanks for the effrort you guys put ..i appreciate it .thanks..

LearnByMistk · ‎05-05-2015

Hi, Is this possible to run a query from already written query like select distinct salary from (select user_id, sum(salary) from emp where month =3 group by user_id) ; this is an example so consider we are having 10 different sum of salary ...but i want to figure that out from select * from select query .... Thanks for reverting back ..

LearnByMistk · ‎04-06-2015

got it by my own ....thanks for your effort

LearnByMistk · ‎04-06-2015

data have; input acc$ sub accs$ ; cards; a 1 d a 2 d a . a a . d b 3 a b 4 d c . a d . a ; run; data want ; c . a; d .a; run; proc sql; select count(acc),acc from test where sub is null group by acc ; quit;

LearnByMistk · ‎01-08-2015

Hi , You can use tranward option to that particular column, as The TRANWRD function differs from TRANSLATE in that it scans for words (or patterns of characters) and replaces those words with a second word (or pattern of characters). hope it might be useful.

Online Status	Offline
Date Last Visited	‎03-07-2021 09:59 AM

Re: update all columns of specific naming convention across monthly da...

update all columns of specific naming convention across monthly datase...

Re: SAS special character in column names

Re: SAS special character in column names

SAS special character in column names

Re: increment month in do loop macro

Re: increment month in do loop macro

Re: increment month in do loop macro

Re: increment month in do loop macro

increment month in do loop macro

Re: update all columns of specific naming convention across monthly da...

Re: Import CSV into SAS

Re: Import CSV into SAS

Re: How many dataset we can use in one to one merge in sas data step .

Re: How many dataset we can use in one to one merge in sas data step .

memsize option while running sas via batch mode

Re: Help SAS date code to distinguish dates and blanks

Re: Writing on listing file

Re: Sql grouping & summation

Sql grouping & summation

Re: case when

Re: case when

Re: case when

case when

Pass through sql or Libname

Re: Proc sql issue

Re: Proc sql issue

Proc sql issue

Re: Select from select query

Select from select query

Re: Group By

Group By

Re: Replacing " " with a "_" for variable names in a given file