PGStats Tracker

Re: Randomly select 3 rows with condition

PGStats — Sat, 24 Feb 2024 20:46:28 GMT

"samplingunit" and "cluster" are synonyms, see the documentation.

Re: Randomly select 3 rows with condition

PGStats — Sat, 17 Feb 2024 22:26:55 GMT

Or equivalently,

proc surveyselect data=have out=temp seed=123 sampsize=3 noprint;
samplingunit dept_no;
proc surveyselect data=temp out=want seed=123 sampsize=1 noprint;
strata dept_no;

Note: May be a bit faster if you have more distinct depts than emp by dept.

Note: Sorting is implied in the first selection step.

Re: Requête inner join conditional

PGStats — Sat, 17 Feb 2024 21:25:41 GMT

Si j'ai bien compris le problème, il faudrait remplacer les jointures internes par des jointures externes.

select
	pop.pop_id, pop.date, ... ,
	pop.geo_id, 
	coalesce(jur.var1, geo.var1) as var1,
	coalesce(jur.var2, geo.var2) as var2, ...
from
	pop 
		left join jur on pop.pop_id=jur.id and pop.date=jur.date and ...
		left join geo on pop.geo_id=geo.id and pop.date=geo.date and ...;
quit;

Re: How to change order of variables in the graph produced by lines in glimmix

PGStats — Sat, 17 Feb 2024 20:53:08 GMT

The Lines graph must represent the class levels ordered by Estimate values. Otherwise, how would it show, for example, in your graph, the link between levels N15 and N200 if they weren't adjacent?

Re: Duplicate value within ID

PGStats — Thu, 25 Jan 2024 20:49:29 GMT

A datastep solution:

/* Assuming data is sorted by study_id */
data want;
do until(last.study_id);
    set have; by study_id;
    newdate = coalesce(newdate, swabdate);
    end;
do until(last.study_id);
    set have; by study_id;
    output;
    end;
format newdate mmddyy10.;
drop swabdate;
rename newdate=swabdate;
run;

Note: This pics up the first non-missing date encountered. To get the last date, use:

coalesce( swabdate, newdate).

Re: Notation scientifique dans une variable de type alfanumerique $char10.

PGStats — Thu, 11 Jan 2024 19:50:26 GMT

Je crois que l'ID "6.09E+203" correspond à un ID original qui avait le malheur de ressembler à un nombre exprimé en notation scientifique et que Excel a automatiquement converti en nombre.

Mais puisque plusieurs IDs, tels que "609E201", "6086E200", "6092E200" ou "60905E199" par exemple, seront convertis dans le même ID "6.09E+203" par Excel, il est impossible de retrouver avec certitude l'ID original.

Il vous faudra donc, comme suggéré ci-haut, remonter à la source pour recouvrer les ID originaux.

Re: Make new variables using if statements in proc sql

PGStats — Tue, 31 Oct 2023 19:37:48 GMT

Just for fun. If you really insist on using SQL :

data have;
input ID $   users_2016  users_2017  users_2018  users_2019  users_2020;
datalines;
jjones    1   1   0   0   0
asmith    0   1   1   1   0
;

proc sql;
create table want (year num, nbUsers num);
insert into want select 2016, count (distinct ID) from have where users_2016;
insert into want select 2017, count (distinct ID) from have where users_2017;
insert into want select 2018, count (distinct ID) from have where users_2018;
insert into want select 2019, count (distinct ID) from have where users_2019;
insert into want select 2020, count (distinct ID) from have where users_2020;
select * from want;
quit;

😁

Re: How to use GROUP BY to concatenate strings in SAS proc SQL?

PGStats — Sat, 28 Oct 2023 21:45:20 GMT

A useful coding tool for doing this is the DO UNTIL() loop

proc sort data=sashelp.class out=class; 
by sex age; 
run;

data want (keep=sex age names);
length names $200;
do until(last.age);
    set class; by sex age;
    names = catx(", ", names, name);
    end;
run;

proc print data=want; 
var sex age names; 
run;

Note that the CATX function trims its arguments, so there is no need to call TRIM.

You can join a long outstanding (since 2017) request for this functionnality in SAS/SQL at

https://communities.sas.com/t5/SASware-Ballot-Ideas/Add-GROUP-CONCAT-function-to-proc-sql/idi-p/323092

Re: Need to remove the exponential from the number

PGStats — Thu, 31 Aug 2023 20:41:32 GMT

Impressive reverse engineering @FreelanceReinh !

Kudos!

Re: how can get help about "proc split" online ?

PGStats — Wed, 05 Jul 2023 03:41:04 GMT

It has been replaced by proc hpsplit.

Re: What will be the output if we do left join and Right join on the below table

PGStats — Mon, 03 Jul 2023 18:57:22 GMT

A decent technical description of the SQL join operations (including the effect of missing (null) values) is given here.

Re: PROC NLIN failed to converge

PGStats — Wed, 28 Jun 2023 18:39:21 GMT

The variable _STATUS_ in the OUTEST= dataset tells you which estimates are final (properly converged) or not.

Re: what's wrong with this code & how to impute date with end date of month if date part not a

PGStats — Fri, 23 Jun 2023 19:44:53 GMT

Incorrect syntax... AND is a logical operator it cannot be used to mean Do this AND that

Replace

if date_datepart eq '' and date_monthpart eq '' then date_datepart='01' and date_monthpart = 'DEC';

with

if date_datepart eq '' and date_monthpart eq '' then do;
date_datepart='01';
date_monthpart = 'DEC';
end;

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

PGStats — Mon, 12 Jun 2023 20:16:22 GMT

These network (graph) statistics are now available from PROC OPTGRAPH, for which you need the SAS Network Algorithms license.

From the procedure documentation, you only need a links (i.e. from-to pairs) dataset to get it going.

The centrality statistics are requested with the CENTRALITY statement.

Re: converting an edgelist dataset to an adjacency matrix, setting missing values as 0

PGStats — Sun, 11 Jun 2023 22:40:05 GMT

This page references 7 different measures of node centrality.

Which centrality measure do you want to calculate? And do you want to do the calculation with SAS or simply provide a well formatted file to other software?

Re: Two different ways to generate random numbers -the same or different ?!

PGStats — Mon, 22 May 2023 04:37:51 GMT

to get random numbers with method 2 you would need something like:

%let n=5000000;

array _x{&n.} _temporary_;

if _n_ = 1 then do step = 1 to &n.;
	_X{step} = MY - SCALE*LOG(-LOG(Step));
	end;

do step = 1 to &n.;
	X = _X{rand("integer", &n.)};
	output;
	end;

those would look almost OK, as long as n is large, but wouldn't save any significant CPU resources.

Re: Number of visit per patient

PGStats — Wed, 17 May 2023 19:06:33 GMT

Both codes can accomodate this change:

data wantPG;
do i = 1 by 1 until (last.name);
    set readin; by name notsorted;
    end;

length v $16;
select (i);
    when (1) v = "1 visit";
    when (2) v = "2 visits";
    otherwise v = "3 visits or more";
    end;
    
drop i visit;
rename v = visit;
run;

proc format;
value visits
  1 = "1 visit"
  2 = "2 visits"
  other = "3 or more visits"
;
run;

proc sql;
create table wantKB as
  select
    max(id) as maxId,
    name,
	max(visit) as lastVisit format=mmddyy10.,
    count(*) as visit format=visits.
  from readin
  group by name
;
quit;

Re: Number of visit per patient

PGStats — Wed, 17 May 2023 03:21:59 GMT

I suppose you want to do something like this:

data want;
do i = 1 by 1 until (last.id);
    set have; by id;
    end;

length v $16;
select (i);
    when (1) v = "1 visit";
    when (2) v = "2 visits";
    otherwise v = "3 visits or more";
    end;
    
drop i visit;
rename v = visit;
run;

Re: How to I set 2 consecutive observations before and after a dummy equal to one

PGStats — Mon, 15 May 2023 03:29:50 GMT

With a single SQL step:

   proc sql;
   create table want as
   select 
    *,
    case when exists (
        select * 
        from trades 
        where intck("day", a.date, tradingDate) between -2 and 2
        )   then 1
            else . end as what_I_want
   from trades as a
   ;
   quit;

Re: optimized code for cartesian product

PGStats — Thu, 11 May 2023 17:10:09 GMT

What operation are you trying to perform (in words).