pradeepalankar Tracker

Re: Filling missing values

pradeepalankar — Fri, 25 Oct 2019 11:31:52 GMT

another try:

proc sql;
create table want as
select a.ID1 as ID1, COALESCE(a.ID2,b.ID2) as ID2
from
have a
left join
(select distinct ID1,ID2 from have where ID2 IS NOT MISSING) b
ON a.ID1=b.ID1;
quit;

Re: creating dummy variables based on string part

pradeepalankar — Wed, 13 May 2015 10:40:41 GMT

you may use this also:

data want;

set have;

CEO_dummy=IFN(index(upcase(role),'CEO'),1,0);

INDEPENDENT_dummy=IFN(index(upcase(role),'INDEPENDENT'),1,0);

run;

via sql:

proc sql;

create table want as

select role,

case when index(upcase(role),'CEO') then 1 else 0 end as CEO_dummy,

case when index(upcase(role),'INDEPENDENT') then 1 else 0 end as INDEPENDENT_dummy

from have;

quit;

Re: Proc SQL and select count(*)

pradeepalankar — Wed, 15 Apr 2015 04:03:25 GMT

try this , if your data is not already sorted then you may have to sort it:

data have;

input month year ATC nb_remb;

cards;

1 2006 1 1234

1 2006 2 3456

1 2006 3 7890

1 2006 5 9876

;

data want;

set have;

by atc;

if dif(atc)>1 then do;

month=month;year=year;atc=atc-1;nb_remb=0;

output;

set have point=_N_;output;

end;

else output;

run;

Re: How we create only specific row(observations)/How do we create row at every NEW first row in the dataset?

pradeepalankar — Fri, 10 Apr 2015 07:35:30 GMT

data want;

set have;

by product;

if first.product then do;

output;

product=product;

rank=0;

date_1=date_2;

date_2=date_2;

value_a=value_b;

value_b=Value_b;

end;

output;

run;

proc sort data=want;

by product rank;

run;

Re: Displaying count in a proc export statement

pradeepalankar — Fri, 10 Apr 2015 04:06:51 GMT

if you just want to put count of rows in the file name not split it on some variable then you may use this:

data source_table;

input Ln region :$5.;

cards;

11 west

12 east

13 north

15 south

;

proc sql;select count(ln) into :cnt from source_table;quit;

data _null_;

File "Serverpath\file__%TRIM(&cnt.).txt" lrecl=30000 dsd dlm='|' termstr=crlf ;

set source_table;

put LN region;

run;

Display "X" at cross section in crosstab report

pradeepalankar — Fri, 27 Feb 2015 10:26:58 GMT

Hi,

I want to display "X" in the cell at the crosssection of crosstab report, by default it is showing frequency number as 1. instead of this frequency i want to display "X" at crosssection.

current situation:

X/Y	a	b	c
aa	1
bb		1
cc			1
dd

desired output:

X/Y	a	b	c
aa	X
bb		X
cc			X
dd

Re: check duplicates

pradeepalankar — Wed, 25 Feb 2015 08:07:28 GMT

Duplicates can be removed as suggested by Reeza.

data students;

input student_id test_id line_id;

cards;

1 2345 1

1 2345 2

1 3468 1

1 3468 2

1 3468 3

2 7896 1

2 1234 1

2 1234 2

2 3452 1

2 3452 2

;

proc sort data=students dupout=student_dups nodupkey;

by student_id test_id;

run;

but what you want in variable X is not clear, please provide example output dataset.

Re: Restructure the table

pradeepalankar — Mon, 05 May 2014 11:17:06 GMT

try this,

data have;

input Questions :$10. Dummy : Score ;

cards;

q7 0 -0.17

q7 1 0.085

q8 0 -0.24

q8 1 0.085

q9 0 -0.19

q9 1 0.07

q10 0 -0.22

q10 1 0.077

q11 0 -0.21

q11 1 0.043

q12 0 -0.28

q12 1 0.08

;

run;

proc transpose data =have out=want(drop=_name_);

by questions notsorted;

id dummy;

var score;

run;

Re: Data Seperation

pradeepalankar — Wed, 30 Apr 2014 08:28:44 GMT

try this,

Data a1;

length product $10000;

Product="car; tv; fridge; washingmachine; mobile"; output;

product="Car; fridge; phone"; output;

Product="car1; tv; fridge; washingmachine; mobile;car; tv; fridge; washingmachine; mobile"; output;

Run;

%macro test;

data a1;set a1;

dlm= countw(product,';');

run;

proc sql noprint;

select max(dlm) into :max_dlm from a1;

quit;

data want;

set a1;

%do i=1 %to &max_dlm.;

product&i=scan(product,&i,';');

%end;

run;

%mend;

%test;

Re: How to flag a group that has at least one particular value

pradeepalankar — Thu, 10 Apr 2014 07:41:44 GMT

data have;

input name $ score;

datalines;

a 1

a 2

a 3

a .

a 1

b 2

b .

b 4

c .

c 1

c 4

;

run;

proc sql;

select distinct name, case when name in (select distinct name from have where score=1) then 'yes'

else 'no' end as yesno from have;

quit;

Re: Import space delimited text if column values contain spaces

pradeepalankar — Tue, 08 Apr 2014 10:02:05 GMT

data want(drop=line);

infile datalines truncover;

input line $1000.;

obs=scan(line,1,' ');

road =substr(substr(line,length(scan(line,1,' '))+1),1,index(substr(line,length(scan(line,1,' '))+1),scan(line,-2,' '))-1);

paint=scan(line,-2,' ');

bright=scan(line,-1,' ');

datalines;

1 Center St. 1 43

2 Broadway 1 46

3 Main St. 1 47

4 Main St. 3 54

5 Elm St. 1 55

6 Station Rd. 1 56

7 Center St. 1 59

8 Center St. 4 61

9 Main St. 3 62

10 Center St. 4 62

11 Park Dr. 3 63

12 Main St. 2 64

13 Park Dr. 1 64

14 Broadway 4 64

15 Broadway 2 64

16 Broadway 3 65

17 Station Rd. 3 67

18 Station Rd. 3 67

19 Elm St. 3 68

20 Beech St. 4 71

21 Elm St. 4 72

22 Beech St. 2 75

23 Beech St. 4 75

24 Beech St. 2 76

25 Park Dr. 4 77

26 Elm St. 2 79

27 Station Rd. 2 79

28 Park Dr. 2 84

;

run;

Re: Selecting Zipcodes starting with a character symbol

pradeepalankar — Wed, 02 Apr 2014 08:40:05 GMT

it should be

if anyalpha(zip)=1;

1 is for if zip starts with character value.

Re: How To Count the number of weekends between two date columns

pradeepalankar — Tue, 01 Apr 2014 12:12:51 GMT

there are actually two data steps used in the code, in second datastep you can set your dataset, ignore the previous data set with datalines.

Re: How To Count the number of weekends between two date columns

pradeepalankar — Tue, 01 Apr 2014 11:36:21 GMT

this link also serve the same purpose

http://blogs.sas.com/content/sasdummy/2011/05/09/calculating-the-number-of-working-days-between-two-dates/

just few modification were needed to get your desired output.

Re: How To Count the number of weekends between two date columns

pradeepalankar — Tue, 01 Apr 2014 11:31:17 GMT

Hi It would have been lot more simpler if you had provided this in begining, while you created the post, we were counting no of weekend days and your requirement if no of days without weekends:

you might have done this with little changes to the code provided earlier:

data have;

input dateid orderdate date9. deliverydate :date9.;

format orderdate deliverydate date9.;

datalines;

1 22MAR2014 24MAR2014

2 23MAR2014 25MAR2014

3 21MAR2014 24MAR2014

4 20MAR2014 25MAR2014

5 23MAR2014 26MAR2014

;

data want(drop=i);

set have;

DesiredOutputTotalDays=0;

CurrentTotalDays=0;

do i=orderdate to deliverydate;

if weekday(i) not in (1,7) then DesiredOutputTotalDays+1;

CurrentTotalDays+1;

end;

by dateid;

if last.dateid then output;

put (orderdate deliverydate CurrentTotalDays DesiredOutputTotalDays)(=);

run;

orderdate=22MAR2014 deliverydate=24MAR2014 CurrentTotalDays=3 DesiredOutputTotalDays=1

orderdate=23MAR2014 deliverydate=25MAR2014 CurrentTotalDays=3 DesiredOutputTotalDays=2

orderdate=21MAR2014 deliverydate=24MAR2014 CurrentTotalDays=4 DesiredOutputTotalDays=2

orderdate=20MAR2014 deliverydate=25MAR2014 CurrentTotalDays=6 DesiredOutputTotalDays=4

orderdate=23MAR2014 deliverydate=26MAR2014 CurrentTotalDays=4 DesiredOutputTotalDays=3

Re: Comparing dates

pradeepalankar — Mon, 31 Mar 2014 17:15:06 GMT

simpleast approach for your query would be

if max year from startdate <2009 and end date lies in 2009 then its a leaving id.

data test;

input ID (Start1-start3 End1-End3)(:ddmmyy10.);

format Start1-start3 End1-End3 ddmmyy10.;

cards;

1 5/5/2009 6/7/2009 8/5/2009 5/10/2010 6/12/2011 10/12/2013

2 1/1/1995 2/3/2005 . 4/5/2009 5/3/2007 .

3 2/3/2007 8/9/2009 8/5/2009 1/3/2009 12/12/2012 10/12/2013

;

data test;

set test;

if ((max(year(start1),year(start2),year(start3))<2009)

and (year(end1)=2009 or year(end2)=2009 or year(end3)=2009)) then flag=1;

run;

Re: How To Count the number of weekends between two date columns

pradeepalankar — Mon, 31 Mar 2014 09:51:21 GMT

hi,

In above comment you said "Thank you Pradeep. It gives the count of weekends" and then again " if i can get the count of weekend for each row between two date columns i will be good to go."

so isn't it what you are getting from code??

i am confused here .... can you draw eaxmple input and output data sets with columns you need?

Re: Adding rows by condition

pradeepalankar — Sun, 30 Mar 2014 18:46:55 GMT

data have;

input x $ y $ z;

id+1;

cards;

a j 0

b k 1

c l 0

d m 0

e o 1

f p 0

g q 0

h r 0

i s 1

;

proc sort data=have;by descending id;run;

data have;

set have(drop=id);

if z=1 then do; idnew+1;output;

call missing(x,y,z);end;

else;idnew+1;output;

run;

proc sort data=have out=want(drop=idnew);by descending idnew;

run;

Re: How To Count the number of weekends between two date columns

pradeepalankar — Sun, 30 Mar 2014 08:51:16 GMT

hi check this, is this what you want:

data have;

input dateid orderdate :mmddyy. deliverydate :mmddyy.;

format orderdate deliverydate yymmdd.;

datalines;

1 1/5/2014 2/10/2014

2 1/7/2014 3/8/2014

3 2/3/2014 4/4/2014

4 2/10/2014 2/15/2014

5 2/10/2014 3/10/2014

6 3/15/2014 3/23/2014

;

data want(drop=i);

set have;

CountOfWeekend=0;

do i=orderdate to deliverydate;

if weekday(i) in (1) then CountOfWeekend+2;

end;

by dateid;

if last.dateid then output;

put (orderdate deliverydate CountOfWeekend)(=);

run;

output:

-------------

orderdate=14-01-05 deliverydate=14-02-10 CountOfWeekend=12

orderdate=14-01-07 deliverydate=14-03-08 CountOfWeekend=16

orderdate=14-02-03 deliverydate=14-04-04 CountOfWeekend=16

orderdate=14-02-10 deliverydate=14-02-15 CountOfWeekend=0

orderdate=14-02-10 deliverydate=14-03-10 CountOfWeekend=8

orderdate=14-03-15 deliverydate=14-03-23 CountOfWeekend=4

Re: Need help to flag encounters inside other encountes

pradeepalankar — Sat, 29 Mar 2014 19:50:35 GMT

why 111 1/7/2013 1/8/2013 this line has flag 1 and why not 111 2/3/2013 2/4/2013, they also have diff of 1.

or may be you just want to check when admission date and discharge date are same, then you may try below code:

data have;

input MHN ADMITDATE :mmddyy. DISCHARGEDATE :mmddyy.;

format admitdate dischargedate yymmdd.;

if ADMITDATE= DISCHARGEDATE then flag=1;

datalines;

111 1/5/2013 1/10/2013

111 1/7/2013 1/8/2013

111 2/3/2013 2/4/2013

222 2/10/2013 2/15/2013

222 2/10/2013 2/10/2013

222 2/14/2013 2/14/2013

444 5/10/2013 5/11/2013

;

RUN;