topic Random sorting when combining data in SAS Programming

Random sorting when combining data

Q1983 — Mon, 03 Dec 2018 00:41:29 GMT

data have;

length Teammate $50;

input Process $ Teammate $;

datalines

AAAA John

AAAA Charles

BBBB Samuel

run;

input_record

LN PROCESS TEAMMATE

1 AAAA

2 AAAA

3 BBBB

PROC SQL;

CREATE TABLE COMBINE AS

SELECT *

FROM HAVE A

LEFT JOIN INPUT_RECORD B

ON A.PROCESS = B.PROCESS

;QUIT

If I run this code process AAAA will be assigned to teammate John for both 1 and 2. Is there a way to assigned this randomly (John would get 1 and Charles would get 2). The reason for this is because the customer provided a spreadsheet similar to input_record in which multiple people can be assigned the same process.

Re: Random sorting when combining data

kiranv_ — Mon, 03 Dec 2018 01:44:39 GMT

use a merge instead of join

data have;

length Teammate $50;

input Process $  Teammate $;

datalines;

AAAA     John

AAAA    Charles

BBBB  Samuel
;
run;



data
input_record;
input 
LN     PROCESS  $    ;
datalines;
1      AAAA    
2      AAAA
3      BBBB
;
data want;
merge have(in=a) input_record(in=b);
by process;
if a;
run;

that should solve this

Re: Random sorting when combining data

r_behata — Mon, 03 Dec 2018 01:52:42 GMT

A SQL Query would produce a Cartesian product ( 5 rows in the sample output, 2 rows each for the repeated values) , are you sure is that is what you intend ? .

If "John would get 1 and Charles would get 2" is the only criteria use a data step merge instead. Not sure what you meant by "Random" in the example.

Re: Random sorting when combining data

mkeintz — Mon, 03 Dec 2018 16:41:52 GMT

@kiranv_

Given the OP's description of the objective, this solution depends ENTIRELY on

Each dataset having the same order of by-groups. (But that can be solved by a proc sort).
The two datasets having exactly then same number of records per by-group. Otherwise, whichever merged data set has a smaller number of within-by-group records will have its final values "retained" through all the "extra" records from the other data set.
Each dataset having the same order within by-groups. Otherwise, there is no apparent variable to validate a match.

In other words, can the OP trust his data providers to be that consistent? Having a blank teammate variable, which would ordinarily be a match-key, diminishes one's confidence in the data provider.

Re: Random sorting when combining data

kiranv_ — Mon, 03 Dec 2018 17:22:38 GMT

you are so right @mkeintz

Sequence randomly in dataset

Q1983 — Mon, 03 Dec 2018 18:44:47 GMT

data have1;

length Process Teammate $50;

input Process $ Teammate$;

datalines;

POC James

POC Sam

POC Jane

run;

data have2;

length Process Teammate $50 ;

input ln Process $ Teammate $ ;

datalines;

12 POC James

55 POC

60 POC

;run;

data merge1;

merge have1 have2;

if process = 'POC';

run;

OUTPUT

ln Process Teammate

12 POC James

55 POC

60 POC

How can I modify the code to randomly assign the teammate in a sequencr of

James, Sam Jane

ln Process Teammate

12 POC James

55 POC Sam

60 POC Jane

72 POC James

88 POC Sam

99 POC Jane

....etc