New board topics in SAS Support Communities

Dataflux: shceduling process job

HeidiDT — Mon, 31 Oct 2022 06:34:13 GMT

Hi All

I need to schedule my Dataflux jobs to run via CA7. I created a small test process job, with a data job embedded. I then created a shell script to call the process job and created a JCL on the mainframe to run this shell script. The process job gets kicked off, but it is unable to open the embedded data job. I am attaching the log. Please could anyone help?

Many thanks!

DataFlux bulkload to SQL Server with datetimeoffset

HeidiDT — Mon, 31 Oct 2022 06:07:06 GMT

Hi All

I am ding a load of 183m rows from Dataflux into a SQL Server table. The current job is just a normal insert, but I am playing around with the bulk row count option to see if I can't speed it up (it ran for 42 hours). However, I am getting an error with a datetimeoffset field. The field seems to be a character value in Dataflux, so I would have thought it would be coerced into a datetime value, but I get this error:

[4:DEST_ODBC:Consolidated Customer Matchcode] Data Access Plugin - Max. ODBC error count (49999) exceeded. Last error: [22018] [DataFlux][ODBC SQL Server Wire Protocol driver]Invalid character value. Error in parameter 11. (0);[HY008] [DataFlux][ODBC SQL Server Wire Protocol driver]Operation cancelled. Error in parameter 116. (0).

I then created a new date variable and converted the character value to a date field (date DOB DOB=todate(left(DateOfBirth,10)), and mapped that to the datetimeoffset field, but I am still getting the same error. Has anyone had any experience with this, and can offer some suggestions, please?

To complicate matters further, it runs fine locally but gives that error when running on the server, so I assume it is using different ODBC drivers.

Count monthly disenrollment

JHE — Mon, 31 Oct 2022 04:20:46 GMT

I need count monthly disenrollment for 2022.

Data table :

MEMBER_ID	ENROLLMENT	DISENROLLEMNT
A0001	2/1/2021	3/1/2021
A0001	5/29/2021	6/24/2021
A0001	7/1/2021	8/1/2021
B0001	6/3/2021	8/2/2021
B0001	10/1/2021	11/1/2021

How do I have the monthly disenroll , and count by member_ID: as following

1. Member_ID, Disenroll_Month_Flg

A0001 202104

A0001 202107

A0001 202209

B0001 202109

B0001 202112

2. Summary: disenroll by month

month count:

202104 1

202107 1

202109 2

202112 1

3. Summery disenroll by member:

Member Count

A0001 3

B0001 2

assign value for a group under condition

Zhongda — Mon, 31 Oct 2022 03:54:16 GMT

Hi All,

I am new to sas and I was trying to achieve such thing:

here is the dataset:

PERMNO EVTDATE first_event inter_event

10001 8/1/11 1 0

10001 4/2/12 0 1

10002 7/6/98 1 0

10002 10/27/98 0 1

10011 11/13/95 1 0

10011 5/28/96 0 1

10011 5/29/96 0 1

10016 12/9/99 1 0

10016 5/3/00 0 1

10020 4/6/87 1 0

10020 10/21/87 0 1

10020 12/11/87 0 1

10020 4/17/90 1 0

10020 4/24/90 0 1

10028 8/17/99 1 0

10028 3/3/00 0 1

10028 7/14/06 1 0

10028 5/10/07 0 1

I would like to create another column call program and the column will state as:

PERMNO EVTDATE first_event inter_event define a program, and numbers of event in the group

10001 8/1/11 1 0 2

10001 4/2/12 0 1 2

10002 7/6/98 1 0 2

10002 10/27/98 0 1 2

10011 11/13/95 1 0 3

10011 5/28/96 0 1 3

10011 5/29/96 0 1 3

10016 12/9/99 1 0 2

10016 5/3/00 0 1 2

10020 4/6/87 1 0 3

10020 10/21/87 0 1 3

10020 12/11/87 0 1 3

10020 4/17/90 1 0 2

10020 4/24/90 0 1 2

10028 8/17/99 1 0 2

10028 3/3/00 0 1 2

10028 7/14/06 1 0 2

10028 5/10/07 0 1 2

let say for permno 10001, there are two obs and they form a program and the program has two event in a row, so it will be definded as 2 for each of the two obs.

Thank you so much for your help!

Appreciated,

Zhongda

Get hour and minutes of the (current time minus 20 second) into macro variables?

hhchenfx — Mon, 31 Oct 2022 03:36:20 GMT

Hi,

I want to put hour and minute of the time 20 second before current time into macro variable.

The code below might convey my idea better.

Can you please help?

Thanks,

HHC

%let now=%sysfunc(time()       MINUS 20 SECOND);
%let hh=%sysfunc(hour(&now),z2.);
%let mm=%sysfunc(minute(&now),z2.);

%put &hh &mm;

How to use Colon (:) to resolve a condition in Macro

SASuserlot — Mon, 31 Oct 2022 01:49:32 GMT

I am trying to resolve the condition of the macro in my code. I have the two conditions where I want to output the error in log based on the input macro variable 'fld' . If it starts with numeric then it have to put one condition otherwise it need to put another error in log ( most of my names with 'SC' if its not numeric

In my example fld= 101-281 then I am expecting to print 'ERROR: 101281 starts with Numeric", My second one is mostly my name starts with 'SC' how can I control the second condition using the ':' colon in the % if condition to print 'ERROR:&dsn ( numeric values of the 'fld' name) starts with alpha character SC"

I am able to use ':' in dataset condition but not sure how to use it the macro, instead of writing every name in the 'If' Condtion.

Thank you for your inputs.

%macro check (fld=);
*%global output dsn;
%let output = %sysfunc(compress(&fld, "-"));


%if &output ^=: SC %then %do;
	%let dsn= %substr(&output.,1,6);

data _281;
	putlog  "ERROR: &dsn starts with Numeric";
run;

%end;

%else %if &output = :SC %then %do;
	%let dsn= %substr(&output.,3,6);
data _109;
	putlog  "ERROR:&dsn starts with alpha character SC";
run;
%end;

%put &output &dsn;


%mend;

%check (fld= 101-281);
%check (fld= 121-281);
%check (fld= SC100426);
%check (fld= SC100843);
%check (fld= SC102126);
%check (fld= SC105143);

So it is all spam I write ... (or: What on Earth... )

fja — Sun, 30 Oct 2022 22:30:33 GMT

Dear Community!

I find my postings being silently deleted quite immediately after being posted ... 4 out of 7 today. Not only are they deleted, they vanish without a trace in the haze and are neither listed in my profile (under recent postings) nor do I get any feedback that they have been deleted for a certain reason (not speaking of mentioning the reason itself).

I would suggest to at least provide some sort of feedback in cases like that.

It is superfluous to mention that I am pretty upset with that. If you are not interested in my contributions, fair enough ... but you should at least let me know.

Kind regards.

recode catergorical variables

Nyac122 — Sun, 30 Oct 2022 22:12:30 GMT

i am trying to recode my district variables so i can use proc reg and its not working .

WARNING: Limit set by ERRORS= option reached. Further errors of this type will not be printed

data flgrads;
input districts $ 1-9 year $ 10-20 percentage;
datalines;
alachua 2016-2017 82.7
alachua 2017-2018 88
alachua 2018-2019 88.5
alachua 2019-2020 90.4
alachua 2020-2021 86.6
baker 2016-2017 81
baker 2017-2018 75.5
baker 2018-2019 78.8
baker 2019-2020 84.5
baker 2020-2021 85.7
bay 2016-2017 78.0
bay 2017-2018 81.1
bay 2018-2019 82.5
bay 2019-2020 88.5
bay 2020-2021 90.2
bradford 2016-2017 78.9
bradford 2017-2018 89
bradford 2018-2019 87.7
bradford 2019-2020 98.2
bradford 2020-2021 85
brevard 2016-2017 85.9
brevard 2017-2018 88.1
brevard 2018-2019 88.3
brevard 2019-2020 90.3
brevard 2020-2021 90.6
broward 2016-2017 81
broward 2017-2018 84.3
broward 2018-2019 86.2
broward 2019-2020 89.4
broward 2020-2021 89.1
calhoun 2016-2017 80.9
calhoun 2017-2018 86.9
calhoun 2018-2019 87.9
calhoun 2019-2020 89.9
calhoun 2020-2021 93.1
Charlotte 2016-2017 81.0
Charlotte 2017-2018 87.6
Charlotte 2018-2019 86.4
Charlotte 2019-2020 90.4
Charlotte 2020-2021 90.9
Citrus 2016-2017 78.9
Citrus 2017-2018 84.1
Citrus 2018-2019 86.0
Citrus 2019-2020 87.1
Citrus 2020-2021 88.1
Clay 2016-2017 88.4
Clay 2017-2018 91.1
Clay 2018-2019 91.9
Clay 2019-2020 93.4
Clay 2020-2021 92.7
Collier 2016-2017 88.2
Collier 2017-2018 91.9
Collier 2018-2019 91.9
Collier 2019-2020 92.2
Collier 2020-2021 92.7
Columbia 2016-2017 70.7
Columbia 2017-2018 88.4
Columbia 2018-2019 92.4
Columbia 2019-2020 95.4
Columbia 2020-2021 95.6
MiamiDade 2016-2017 80.7
MiamiDade 2017-2018 85.4
MiamiDade 2018-2019 85.6
MiamiDade 2019-2020 89.6
MiamiDade 2020-2021 90.1
DeSoto 2016-2017 63.8
DeSoto 2017-2018 60.9
DeSoto 2018-2019 71.3
DeSoto 2019-2020 84.6
DeSoto 2020-2021 82
Dixie 2016-2017 89.5
Dixie 2017-2018 96.9
Dixie 2018-2019 90.6
Dixie 2019-2020 89.8
Dixie 2020-2021 84
Duval 2016-2017 80.8
Duval 2017-2018 85.1
Duval 2018-2019 86.5
Duval 2019-2020 90.2
Duval 2020-2021 89.6
;
proc print;
run;
data flgrads2;
set flgrads;
Alachua= (district= 0);
Baker= (districts= 1);
Bay = (districts= 2);
Bradford= (districts= 3);
Brevard= (districts= 4);
Broward= (districts= 5);
Calhoun= (districts= 6);
Charlotte= (districts= 7);
Citrus= (districts= 8);
Clay= (districts= 9);
Collier= (districts= 10);
Columbia= (districts= 11);
MiamiDade= (districts= 12);
DeSoto= (districts= 13);
Dixie= (districts= 14);
Duval= (districts= 15);
run;

Fun With SAS ODS Graphics: Happy Halloween Ellipses Pumpkin

tc — Sun, 30 Oct 2022 21:45:58 GMT

Another holiday, another SAS ODS Graphics "craft project". Happy Halloween, all!

* Fun w/SAS ODS Graphics: Happy Halloween Ellipses Pumpkin
  Inspired by neat craft project at itsmejd.com/easy-diy-halloween-cards-make-minimal-supplies;
  
data pumpkin;          * Need one "dummy" point to use ellipseparm statements;
retain x y 0;  
                       * SAS ODS Graphics GTL Halloween greetings;
ods graphics on / reset antialias width=4in height=5.5in;
proc template;         * 5 ellipseparms + 2 drawrectangles + 1 drawtext (and 1 "dummy" scatterplot);
define statgraph pumpkin;
begingraph / backgroundcolor=black border=false pad=0in; 
layout overlay / xaxisopts=(display=none linearopts=(viewmin=-1 viewmax=1) offsetmin=0 offsetmax=0) 
                 yaxisopts=(display=none linearopts=(viewmin=-1 viewmax=1.1) offsetmin=.05 offsetmax=.15) 
                 walldisplay=none border=false outerpad=0in;
scatterplot x=x y=y;    * "Dummy" plot - single point (x=0, y=0), needed for ellipseparm); 
drawrectangle x=.05 y=1 width=.3 height=.6 / layer=back heightunit=data widthunit=data drawspace=datavalue rotate=-20 display=(fill) fillAttrs=(color=cx2EB62C); * Green "stem";
ellipseparm semiminor=.5 semimajor=1 xorigin=-1 yorigin=y slope=. / display=(fill outline) fillattrs=(color=cxFF7518) outlineattrs=(color=black); * Arranged to show desired outlines; 
ellipseparm semiminor=.5 semimajor=1 xorigin=-.5 yorigin=y slope=. / display=(fill outline) fillattrs=(color=cxFF7518) outlineattrs=(color=black); 
ellipseparm semiminor=.5 semimajor=1 xorigin=1 yorigin=y slope=. / display=(fill outline) fillattrs=(color=cxFF7518) outlineattrs=(color=black); 
ellipseparm semiminor=.5 semimajor=1 xorigin=.5 yorigin=y slope=. / display=(fill outline) fillattrs=(color=cxFF7518) outlineattrs=(color=black); 
ellipseparm semiminor=.5 semimajor=1 xorigin=0 yorigin=y slope=. / display=(fill outline) fillattrs=(color=cxFF7518) outlineattrs=(color=black); 
drawrectangle x=0 y=-.15 width=2 height=.5 / anchor=top layer=front heightunit=data widthunit=data drawspace=datavalue display=(fill) fillAttrs=(color=black); 
drawtext textattrs=(size=20pt weight=bold color=white) "HAPPY HALLOWEEN!" /
         layer=front anchor=center justify=center width=100 widthunit=percent xspace=datavalue yspace=datavalue x=0 y=-.4;
endlayout;
endgraph;
end;

proc sgrender data=pumpkin(obs=1) template=pumpkin; * Generate chart!;
run;

BEFORE COLORING

SET BULKLOAD OPTIONS (POSTGRES ENGINE) TO SCD2 TRANSFORMATION IN DIS

Egrodrigues2014 — Sun, 30 Oct 2022 19:49:47 GMT

Hi everyone,

When I set BULK LOAD option as active, SCD2 transformation create the following code (for ETLS_CLOSE as example):

      proc append base = EXT_VI."W6B3BTSM"n
         (
            BULKLOAD=YES
         )
         data = work."ETLS_CLOSE"n force; 
      run;

But this code doesn't work. I need to set "bl_psql_path='/PATH' " and "bl_delete_datafile=no" dataset option to BULKLOAD works properly.

I aware that I can change the automatic generated code to add these options.

Is there a way to configure these options at the transformation level? Or a way that I can add these options without change the code manually?

Thanks in advance.

SAS BI Web services - RESTful JSON response - strign parameters only

NY — Sun, 30 Oct 2022 13:52:19 GMT

Hi Experts,

I have created a stored process with input and output parameters that I'm trying to call using SAS BI Web Services. My endpoint is JSON.

*ProcessBody; 
 
%global BANK 
        CALCULATIONTYPE 
        COMPANYID 
        CUSTOMERID 
        MODELCODE 
        PREVIOUSCALCULATIONDATE 
        PREVIOUSEXPECTEDLOSS 
        PREVIOUSLOSSGIVENDEFAULT 
        PREVIOUSPROBABILITYOFDEFAULT 
        QUESTIONNAIREGRADE; 
 
%STPBEGIN; 
 
*  End EG generated code (do not edit this line); 
 
 
proc printto log=" D:\RiskModels\RestLogs\web_service_test_%sysfunc(today(),yymmdd10.)-%sysfunc(compress(%sysfunc(time( ),time.),":")).log";
   run;
libname srcdata "D:\RiskModels\SourceData";
proc sql noprint;
select distinct Client_LGD format=16.8 into :lgd from srcdata.lgd_table where 
ID =&customerID;
quit;
%let probabilityOfDefault=0.5;
%let lossGivenDefault = &lgd;
%let expectedLoss = %sysevalf(&lgd. * &probabilityOfDefault.);


*  Begin EG generated code (do not edit this line); 
;*';*";*/;quit; 
%STPEND; 
 
*  End EG generated code (do not edit this line);

My output parameters were defined as DOUBLE, but in the response in my API tester tool (POSTMAN for example),

they all show as STRING.

{
    "outputParameters": {
        "expectedLoss": "0.22068032",
        "probabilityOfDefault": "0.5",
        "lossGivenDefault": "0.44136064"
    }
}

When I looked in SAS documentation the example shows a response with string variables only.

Does anyone succeed transfer numeric parameters?

https://documentation.sas.com/doc/en/itechcdc/9.4/wbsvcdg/n1wblekhip1yrln1fv2s5b6a2d9f.htm

Thank you very much,

Nufar.

looking for sas code for proc lifereg and proc mianalyze combined together

redspring — Sun, 30 Oct 2022 09:49:32 GMT

Hi All SAS users,

I am looking for SAS code that can combine proc lifereg with Weibull distribution and proc mianalyze and the proc lifereg starts with the intercept only and adds one variable a time until all variables of interest are added.

I have spent some time searching so far nothing addressing all of the above requirements are found.

Could anyone of you provide the SAS codes or point me to the existing postings or paper please?

Thank you very much in advance.

Do I need to cancel my subscription?

Nietzsche — Sat, 29 Oct 2022 22:06:28 GMT

I signed up for the 30 day free trial, I can not remember if I gave SAS Institute my credit card details or not. I have finished my Programming 2 course with in the 30 days, and I am not impressed to say the least, and I do NOT want to be changed $1200 AUD or any subscription fees after the 30 days end.

How can I cancel the subscription right now as I am not interested in any other courses.

Finding inconsistencies within a group

Sanna_K — Sat, 29 Oct 2022 20:39:09 GMT

Hi.

I have a very large dataset and I need to be able to locate data discrepancies to exclude from my rate calculation. In my example below, column Verd1 is the main identifier and column LocalVerd is another identifier created by a vendor. I need to figure out how to code to identify erroneous data within a group. In the example below, under column Plan, Pacific would pass and is correct as the rows within Pacific contain consistent identifier information (Same Verd1 and same localVerd). However, Plan Delta for product X33ui will need to be excluded as this product (X33ui) contain two different LocalVerd identifiers (PX333 and Rx988) for this same Verd1 identifier (Twa88)- LocalVerd should be PX333 but reported Rx988.

Is there a way to code to output table containing all the Plan and product containing inconsistencies? Data table is below and my output table I would like is the second table. Thank you in advance

Plan	Product	Verd1	LocalVerd
Delta	X33ui	Twa88	PX333
Delta	X33ui	Twa88	PX333
Delta	X33ui	Twa88	RX988
Pacific	V933aa	Lbl322	QT770
Pacific	V933aa	Lbl322	QT770

Wanted OUTPUT
Plan	Product	Verd1	LocalVerd
Delta	X33ui	Twa88	PX333
Delta	X33ui	Twa88	PX333
Delta	X33ui	Twa88	RX988

Now available on SAS Community: SAS Explore 2022 sessions

ChrisHemedinger — Sat, 29 Oct 2022 14:41:22 GMT

All of the great technical content that you saw (or somehow missed) from SAS Explore 2022 is now available here in the SAS Explore hub on SAS Communities. Over 80 presentations -- including SAS training, User Presentations, Super Demos, R&D Breakout sessions and more -- can be found in the SAS Explore Presentations library.

You can also find these sessions along with select plenary presentations on the SAS Video Portal. And watch/subscribe to the SAS Users YouTube channel -- selected presentations are coming there to live alongside regular programming of hundreds of SAS tutorials and other SAS user-focused shows.

Perl-regular expressions in SAS to add imputations to date and highlight data errors

smackerz1988 — Sat, 29 Oct 2022 21:49:48 GMT

Hello,

I'm trying to use perl-regular expressions to impute missing datetime variables to eventually put in a macro as the imputed variable (e.g adtmc) is quite common in datasets. My approach works to a degree but I was wondering is it possible to further expand it to highlight uncommon data problems. Also depending on data raw datetime variables can sometimes contain a space or a T in between date and time. My current code only accounts for spacing but how can I account for both but make sure the T is removed and replaced by a space? I've provided sample data, desired output and the code I'm using currently to apply

data have;
length ecdtc $16;
infile datalines truncover;
input @1 ecdtc $16.;
datalines;
2020-01-01 01:02
2020-01-01T01:02
2020-01-01T01:
2020-01-01T01:89
2020-01-01T
2020-01-01' '/*Space indicates a possible datetime*/
2020-01-02
2020-01
2020
junk
;;;;
run;


data want;
length ecdtc $16;
infile datalines truncover;
input @1 ecdtc $16.;
datalines;
2020-01-01 01:02
2020-01-01 01:02
2020-01-01 01:XX
2020-01-01 01:XX
2020-01-01 XX:XX
2020-01-01 XX:XX 
2020-01-02 
2020-01-XX
2020-XX-XX
junk
;;;;
run;


data want;
  set have;
  length adtmc $16;
  array vals[3] $;
  vals[1]='XXXX';
  vals[2]='-XX';
  vals[3]='-XX';
  _rx = prxparse('/(\d{4})(-\d{2})?(-\d{2})?( \d{2}:\d{2})?/ios');
  _rc = prxmatch(_rx,ecdtc);   *this does the matching.  Probably should check for value of _rc to make sure it matched before continuing.;
  do _i = 1 to 4;   *now iterate through the four capture buffers;
    _rt = prxposn(_rx,_i,ecdtc);
    if _i le 3 then vals[_i] = coalescec(_rt,vals[_i]);
    else timepart = _rt;  *we do the timepart outside the array since it needs to be catted with a space while the others do not, easier this way;
  end;
  
  adtmc = cats(of vals[*]);  *cat them together now - if you do not capture the hyphen then use catx ('-',of vals[*]) instead;
  if timepart ne ' ' then adtmc = catx(' ',adtmc,timepart);  *and append the timepart after.;
run;

DO BLOCK

MK_96 — Sat, 29 Oct 2022 07:57:28 GMT

Data chk;
do name="mk", "pk", "kk", "ck"; output;
do ord=1,2,3,4; output;
end;
end;
Run;

How can i get my desired output like below
______________________
OBS Name Ord
_____________________
1 Mk 1
2 pk 2
3 kk 3
4 ck 4

PROC SURVEYLOGISTIC Domain with interaction term doesn't work?

Niveus — Sat, 29 Oct 2022 04:51:07 GMT

When I tried to include an interaction term (VTQ200A*income) in the PROC SURVEYLOGISTIC statement, it wouldn't include the ORs for the VTQ200A variable when I stratified it by the income variable using the DOMAIN statement. It would only include VTQ200A when VTQ200A*income was not part of the model.

I also tried making another dataset that was exactly same as the other one I was using but had an added variable, VTQ200A_income, that was equal to the interaction term (VTQ200A_income = VTQ200A*income).

I only really learned how to do PROC LOGISTIC and not PROC SURVEYLOGISTIC, so I'm not sure how to account for effect modification and stratification correctly. This was just my attempt using what I learned about PROC LOGISTIC. Since income seems to be an effect modifier of VTQ200A, I want to stratify the analysis by income. Is this even on the right track?

proc surveylogistic data=ile.fulldata3; *adjusted model w/o interaction term;
class VTQ200A (ref="2") RIAGENDR (ref="2") race (ref="1")  income (ref="2") PAlevel (ref='1')/param=ref;
model hbp=VTQ200A income palevel RIAGENDR race  RIDAGEYR DBD895;*using this model w/o VTQ200A*income for now;
weight WTINTPRP;
run;

proc surveylogistic data=ile.fulldata3; *adjusted model w/interaction term_test1;
class VTQ200A (ref="2") RIAGENDR (ref="2") race (ref="1") income (ref="2") PAlevel (ref='1')/param=ref;
model hbp= VTQ200A income RIAGENDR race palevel RIDAGEYR DBD895 VTQ200A*income; *the VTQ200A*income causes the OR for VTQ200A to not show up?;
weight WTINTPRP;
domain income;
run;

data ile.fulldata_int; 
set ile.fulldata3;
VTQ200A_income =VTQ200A*income; run;

proc surveylogistic data=ile.fulldata_int; *adjusted model w/interaction term_test2?;
class VTQ200A (ref="2") RIAGENDR (ref="2") race (ref="1") income (ref="2") PAlevel (ref='1')/param=ref;
model hbp= VTQ200A income RIAGENDR race palevel RIDAGEYR DBD895 VTQ200A_income;
weight WTINTPRP;
domain income;
run; *VTQ200A isnt in the OR table when I add VTQ200A*income, so I made a new var VTQ200A_income. The ORs are the same as the model without VTQ200A*income;

Error create table with data step in SQL Server

SAS_LuisBolivar — Sat, 29 Oct 2022 03:25:37 GMT

Error create table with data step in SQL Server.

ERROR: Error attempting to CREATE a DBMS table. .

It does not indicate an error description and when executing via pass-through it does not fail, but the SCD 2 transformation generates the tables with data passing.

Thanks.

Compile records based on rules

lichee — Sat, 29 Oct 2022 15:36:30 GMT

Hi all,

I wanted to compile records into the target data set "want" based on some simple rules for the data I have "have" as below. The simple rules include

(1) When multiple records per person have the same state info and the complementary Type_CDs, compile them into one record, for example, person1, person2, person 3, and person4.

(2) When multiple records per person have the different state info and their Type_CDs are different, the records cannot be compiled, for example, person5 and person6. However, if one record has no Type_CDs available at all, then the record with any available Type_CDs should be picked, and Person8 is this case.

(3) Any other Type_CD is subset of Type_CD 1. Because of this, any record with Type_CD=1 can overwrite other records per person if the records have the same state info. for example, Person7.

data have;
infile datalines truncover dsd;
input DummyID $ State $ Type_CD1-Type_CD6
;
datalines;
Person1,NJ,,,,15,1,1
Person1,NJ,15,15,15,,,
Person2,NY,7,7,7,7,7,
Person2,NY,,,,,,7
Person3,NY,,,,,,7
Person3,NY,,,,7,7,
Person4,NY,,,,7,7,7
Person4,NY,7,7,7,,,
Person5,NY,7,7,7,7,7,7
Person5,CA,,,,,,4
Person6,NY,7,7,7,7,7,
Person6,NJ,,,,,,15
Person7,AR,1,1,1,1,1,1
Person7,AR,,,7,7,7,7
Person8,NY,,,,1,1,1
Person8,NJ,,,,,,
;
run;

data want;
infile datalines truncover dsd;
input DummyID $ State $ Type_CD1-Type_CD6
;
datalines;
Person1,NJ,15,15,15,15,1,1
Person2,NY,7,7,7,7,7,7
Person3,NY,,,,7,7,7
Person4,NY,7,7,7,7,7,7
Person7,AR,1,1,1,1,1,1
Person8,NY,,,,1,1,1
;
run;

Any suggestions are greatly appreciated.

Thank you!