MikeZdeb Tracker

Re: Create variables as per separators in string

MikeZdeb — Fri, 28 Jul 2017 02:57:15 GMT

Hi, here's another idea. I made the maximum number of diseses five (D1-D5) and the maimum lenght of any disease twenty. You could make those values anythimg you like.

data x;
infile datalines dsd dlm='^' missover;
input @;
substr(_infile_,find(_infile_,' '),1) = '^';
input id :$5. (d1-d5) (:$20.);
count = countc(_infile_,'^');
datalines;
X1 Fever^Acidity^Vomiting
X2 Chills^Nausea
X3 Bad Vibes^Bummed^Just Plain Tired
X1234 Upset Stomach
;

DATA SET: x

id       d1                 d2       d3                  d4    d5    count

X1       Fever            Acidity    Vomiting                          3
X2       Chills           Nausea                                       2
X3       Bad Vibes        Bummed     Just Plain Tired                  3
X1234    Upset Stomach                                                 1

Re: How to create sub-totals ?

MikeZdeb — Thu, 27 Jul 2017 23:08:37 GMT

Hi, how about PROC SUMMARY. Only issue is that the value of the variable LOCATION is missing rather than "REGION" (you can fix that in a subsequent data step). Since there is no ascending or descending order of the location variable in your output, you'd have to play games with the values to get the order you show. (e.g. leading spaces) ...

data x;
input race :$5. group :$3. location :$6. date :monyy. count;
format date monyy.;
datalines;
White All LA Aug-16 1
White All ANA Aug-16 2
Black All LA Aug-16 2
Black All ANA Aug-16 3
Black 65P ANA Aug-16 1
;

proc summary data=x;
class race group location date;
var count;
output out=y (drop=_: )sum=;
types race*group*location*date race*group*date;
run;

data y;
set y;
location = ifc(missing(location),'Region',location);
run;

DATA SET: y

race     group    location    date     count

Black     65P      Region     AUG16      1
Black     All      Region     AUG16      5
White     All      Region     AUG16      3
Black     65P      ANA        AUG16      1
Black     All      ANA        AUG16      3
Black     All      LA         AUG16      2
White     All      ANA        AUG16      2
White     All      LA         AUG16      1

Re: Running a function for each unique value

MikeZdeb — Fri, 14 Jul 2017 03:13:18 GMT

Hi, another idea ...

data have;
input id x :$7. @@;
datalines;
1 BMI 1 CHOL 1 GLUCOSE 1 BP
2 BMI 2 CHOL 3 BMI 3 CHOL
3 GLUCOSE 3 OTHER 4 BMI 4 CHOL
4 GLUCOSE 4 BP 4 OTHER 4 DUDE
;

* find maximum number of observations within an ID;

proc sql noprint;
select max(count) into :max from
(select count(*) as count from have group by id);
quit;

* reshape data, one observation per ID;

proc summary data=have nway;
class id;
output out=have2 (drop=_: ) idgroup(out[&max] (x)=);
run;

data want;
set have2;
array x_(&max);
all4 = ('BMI' in x_) & ('CHOL' in x_) & ('GLUCOSE' in x_) & ('BP' in x_);
run;

DATA SET: want

id    x_1    x_2       x_3      x_4       x_5     x_6     all4

 1    BMI    CHOL    GLUCOSE    BP                          1
 2    BMI    CHOL                                           0
 3    BMI    CHOL    GLUCOSE    OTHER                       0
 4    BMI    CHOL    GLUCOSE    BP       OTHER    DUDE      1

Or, using a bit of Art's approach ...

data want (keep=id all4);
array x_(50) $7;
do j=1 by 1 until(last.id);
set have;
by id;
x_(j) = x;
end;
all4 = ('BMI' in x_) & ('CHOL' in x_) & ('GLUCOSE' in x_) & ('BP' in x_);
run;

DATA SET: want

id    all4

 1      1
 2      0
 3      0
 4      1

Re: Aggregating dataset by date, including missing dates

MikeZdeb — Mon, 10 Jul 2017 22:23:43 GMT

Hi, still needs a couple fixes, only worked since the max date is in the first group of addresses.

Will fix later and send to you. My email is msz03@albany.edu

Re: Aggregating dataset by date, including missing dates

MikeZdeb — Mon, 10 Jul 2017 18:05:25 GMT

For maximum date in the data set for all PROPERTIES ...

data y;
retain max_date;
array _a(0:1000);
do j=1 by 1 until (last.address);
set xx;
by address;
if first.address then first = date;
_a(date - first) = amount;
max_date = max(max_date, date);
end;
do j=0 to max_date-first;
value = sum(value,_a(j));
date = first+j;
if day(date+1) eq 1 then output;
end;
keep address date value;
run;

Send me an email ... msz03albany.edu ... and I'll do the walk through for you.

Re: Aggregating dataset by date, including missing dates

MikeZdeb — Mon, 10 Jul 2017 18:03:15 GMT

Hi, SPARSE does not add missing datesd to the data set ... yes/no? PROC FREQ can only count values that are present in the data. SPARSE only insures that table cells with ZERO values end up in a data set

Re: Aggregating dataset by date, including missing dates

MikeZdeb — Mon, 10 Jul 2017 17:44:38 GMT

Hi, this uses an array but not in the way (I think) that you proposed. There probably is a more efficient INTNX-related metod, but this fills in the missing months ...

* aggregate amounts by address and date;

proc summary data=x nway;
class address date;
var amount;
output out=xx (drop=_:) sum=;
run;

* fill in the missing months;

data y;
array _a(0:1000);
do j=1 by 1 until (last.address);
set xx;
by address;
if first.address then first = date;
_a(date - first) = amount;
end;
do j=0 to (date - first);
value = sum(value,_a(j));
date = first+j;
if day(date+1) eq 1 then output;
end;
keep address date value;
run;

Portion of output ...

DATA SET: y

 address           date       value

Property1    31/01/2014     -73649.60
Property1    28/02/2014     -73649.60
Property1    31/03/2014     -73649.60
Property1    30/04/2014     -73649.60
Property1    31/05/2014     -73649.60
Property1    30/06/2014     -73649.60
Property1    31/07/2014     -74309.60
Property1    31/08/2014     -74309.60
Property1    30/09/2014     -74309.60
Property1    31/10/2014     -76047.60
Property1    30/11/2014     -76047.60
Property1    31/12/2014     -76047.60
Property1    31/01/2015     -76047.60
Property1    28/02/2015     -76047.60
Property1    31/03/2015     -76047.60
Property1    30/04/2015     -76047.60
Property1    31/05/2015     -76047.60
Property1    30/06/2015     -76892.92
Property1    31/07/2015     -76892.92
Property1    31/08/2015     -76892.92
Property1    30/09/2015     -76892.92
Property1    31/10/2015     -76892.92
Property1    30/11/2015     -76892.92
Property1    31/12/2015     -76047.60

Re: How to write generic libname to multiple databases?

MikeZdeb — Fri, 07 Jul 2017 23:12:53 GMT

Hi, if it's all the data sets in that library, here's an example ...

libname z 'z:\';

data z.one;
retain x 10;
run;

data z.two;
retain x 10;
run;

* concatenate the data set names into a macro variable;

proc sql;
select catt('z.',memname) into :dsets separated by ' '
from dictionary.tables where libname = 'Z';
quit;

data new;
set &dsets;
run;

If your data sets had a common numeric suffix, it's easier ...

data z.d1;
retain x 10;
run;

data z.d2;
retain x 10;
run;

data new;
set z.d1-z.d2;
run;

Re: delete/retein observations by frequency (n) of variables

MikeZdeb — Fri, 07 Jul 2017 22:45:59 GMT

Hi, another idea if there are a lot of different values of VARIABLES 1 and 2 ...

proc sql;
create table fmt as
select "$v1_" as fmtname, var1 as start, "ok" as label from have
group by var1 having count(*) ge 3
union
select "$v2_" as fmtname, var2 as start, "ok" as label from have
group by var2 having count(*) ge 3;
quit;

* uncomment SELECT if you want to see the formats;

proc format cntlin=fmt;
*select $v1_ $v2_;
run;

data want;
set have;
if put(var1,$v1_.) eq "ok" and put(var2,$v2_.) eq "ok";
run;

Re: delete/retein observations by frequency (n) of variables

MikeZdeb — Fri, 07 Jul 2017 20:42:54 GMT

Hi, another idea that also use PROC SQL first (I see you have variable1 & 2 as caharcter data so the values are quoted in SQL)...

proc sql;
select quote(var1) into :v1 separated by ' ' from have group by var1 having count(*) ge 3;
select quote(var2) into :v2 separated by ' ' from have group by var2 having count(*) ge 3;
quit;

data want;
set have;
if var1 in (&v1) and var2 in(&v2);
run;

DATA SET: want

  id        var1      var2

9914108    100671     91996
9914115    100690     91999
9914119    100690     91996
9914124    9512799    91996
9914130    100671     91996
9914136    9512799    91996
9914158    100690     91999
9914163    100671     91999
9914164    9512799    91999

Re: data manipulation in SAS

MikeZdeb — Tue, 04 Jul 2017 17:15:19 GMT

Hi, just one more tweak ... if you want to make sure that you always get the full duration of all the IDs in your data set you could use a macro variable that contains the maximum difference between first and last days within an ID in your data ...

proc sql noprint;
select max(diff) into :max trimmed from
(select max(date)-min(date) as diff from x group by id);
quit;

data z (drop=first s: diff);
array s(0:&max) s0-s&max;
do until(last.id);
set x;
by id;
if first.id then first = date;
s(date - first) = date;
end;
diff = coalesce(of s&max-s0)-s0;
do after=0 to ifn(diff gt 14, diff, 14);
date = s(after);
day = first+after;
output;
end;
format day date9.;
run;

Re: data manipulation in SAS

MikeZdeb — Tue, 04 Jul 2017 17:01:58 GMT

Sure, added DAY to the data set ...

data z (drop=first s: diff);
array s(0:30) s0-s30;
do until(last.id);
set x;
by id;
if first.id then first = date;
s(date - first) = date;
end;
diff = coalesce(of s30-s0)-s0;
do after=0 to ifn(diff gt 14, diff, 14);
date = s(after);
day = first+after;
output;
end;
format day date9.;
run;

Re: data manipulation in SAS

MikeZdeb — Tue, 04 Jul 2017 16:33:29 GMT

Hi, using Art's data, here's another idea that get's the same results as Art's last posting ...

data x;
input id date date9. @@;
format date date9.;
datalines;
1 16Aug2013
2 07Aug2013 2 09Aug2013
3 10Aug2013 3 20Aug2013 3 30Aug2013
;

data y (drop=first s: diff);
array s(0:30) s0-s30;
do until(last.id);
set x;
by id;
if first.id then first = date;
s(date - first) = date;
end;
diff = coalesce(of s30-s0)-s0;
do after=0 to ifn(diff gt 14, diff, 14);
date = s(after);
output;
end;
run;

Re: how to get number of distinct elements in each row without nested if-else loops

MikeZdeb — Tue, 04 Jul 2017 03:03:10 GMT

Hi, another (belated) idea though you end up with sorted names ...

data x;
input (name1-name4) (:$2.);
datalines;
AA BB CC DD
AA AA BB EE
FF FF CC CC
AA AA AA AA
BB BB BB ZZ
AA CC CC AA
JJ KK KK KK
ZZ YY ZZ YY
QQ QQ EE FF
;

data y (drop=names);
length names $30;
set x;
call sortc(of name1-name4);
names = catx(' ',of name: );
howmany = (count(tranwrd(names,name1,''),name2) ne 0) +
(count(tranwrd(names,name2,''),name3) ne 0) +
(count(tranwrd(names,name3,''),name4) ne 0) + 1;
run;

Or ...

data y (drop=j);
set x;
array name(4);
call sortc(of name1-name4);
howmany = 1;
do j=1 to 3;
howmany + ^^find(tranwrd(catx(' ',of name: ),name(j),''),name(j+1));
end;
run;

(^^find ... changes result from location to 1 if found, 0 if not ... thanks to Howard Schreier)

DATA SET: y

name1    name2    name3    name4    howmany

 AA       BB       CC       DD         4
 AA       AA       BB       EE         3
 CC       CC       FF       FF         2
 AA       AA       AA       AA         1
 BB       BB       BB       ZZ         2
 AA       AA       CC       CC         2
 JJ       KK       KK       KK         2
 YY       YY       ZZ       ZZ         2
 EE       FF       QQ       QQ         3

Re: Adding one variable to dataset

MikeZdeb — Mon, 03 Jul 2017 18:26:44 GMT

And you can do some reading for more explanation ...

Transposing Data Using PROC SUMMARY'S IDGROUP Option (John King, Mike Zdeb)

http://support.sas.com/resources/papers/proceedings10/102-2010.pdf

Re: Adding one variable to dataset

MikeZdeb — Mon, 03 Jul 2017 15:32:47 GMT

Hi, another idea (could rename DATE_1 and ID variables with PROC DATASETS or as a data set option for data set Y in PROC SUMMARY) ...

data x;
input member id :$1. date :date9.;
format date date9.;
datalines;
101 1 12Jun2017
102 3 14May2017
101 2 19Jul2017
103 5 24Jun2017
102 4 13May2017
;

proc summary data=x nway;
class member;
output out=y (keep=member id: date_1) idgroup(out[2] (date id)=);
run;

DATA SET: y

member date_1 id_1 id_2

101 12JUN2017 1 2
102 14MAY2017 3 4
103 24JUN2017 5

Re: SAS_Count Frequency

MikeZdeb — Sun, 02 Jul 2017 17:47:28 GMT

Hi, another idea (not 1 million groups, but 12 million observations ... SUMMARY + TRANSPOSE juts a few seconds). I don't see in your exmaple output the need to rank counts within groups.

data x;
input (group category) (:$1.) @@;
datalines;
A 1 A 1 A 2 A 1 A 2 A 3
B 1 B 2 B 2 B 1 B 3 B 2
;

data bigx;
set x;
do _n_=1 to 1e6;
output;
end;
run;

proc summary data=bigx nway;
class group category;
output out=y (drop=_type_);
run;

proc transpose data=y out=z (drop=_name_) prefix=cat;
by group;
var _freq_;
id category;
run;

DATA SET: z

group cat1 cat2 cat3

A 3000000 2000000 1000000
B 2000000 3000000 1000000

Re: How to remove prefix number 1 after proc transpose?

MikeZdeb — Sun, 02 Jul 2017 17:19:39 GMT

Hi, same as Art's ... seems like just as much effort without the macro variable ...

data x;
input name :$5. bd_dx_1-bd_dx_7;
datalines;
bob 1 2 3 4 5 6 7
carol 9 9 9 9 9 9 9
ted 0 0 0 0 0 0 0
alice 7 6 5 4 3 2 1
;

proc transpose data=x out=trans (drop=_name_ rename=(col1=bd_dx));
var bd_dx: ;
by name notsorted;
run;

PORTION OF DATA SET: trans

name bd_dx

bob 1
bob 2
bob 3
bob 4
bob 5
bob 6
bob 7
carol 9
carol 9

Re: problem with sas code

MikeZdeb — Sun, 02 Jul 2017 17:08:50 GMT

Hi, curious ... why use a loop in this example ...

data want;
do until(date ne .);
set have;
date=intnx('day','01jul2017'd,_n_);
end;
format date date9.;
run;

Why not just ...

data want;
set have;
date=intnx('day','01jul2017'd,_n_);
format date date9.;
run;

Re: problem facing while infile the CSV file.

MikeZdeb — Sun, 02 Jul 2017 16:48:49 GMT

Hi, you can cut down on the amount of SAS code ...

#1 the DSD option implies a comma delimiter so the DLM option is not needed

#2 your dates are in standard forms so the ANYDTDTE informat works for both

#3 the default length of numeric variables is 8, so the LENGTH statement is not needed

#4 it's just LIST input, so you can add the INFORMAT in the INPUT statement and leave out the INFORMAT statement

data a;
infile "z:\test.csv" dsd firstobs=2;
format dob enroll yymmdd10. ;
input cc (dob enroll) (:anydtdte.);
run;