topic Re: PROC FORMAT in SAS Procedures

PROC FORMAT

robertrao — Fri, 15 Mar 2013 18:33:14 GMT

I have two datasets.

DATASET2 is a database.

I want to find only the info about missing records from DATASET2(with var1 the DATASET2 might have multiple recorde)

They have var1 in common

How can i use the CNTLIN to acheive this????

Is there any other easier??

DATASET1 DATASET2
var1 var2 var1 ....................

101 a

102 b

103 missing

104 missing

105 missing

106 missing

107 f

Re: PROC FORMAT

DBailey — Fri, 15 Mar 2013 19:42:56 GMT

I'm not sure how proc format applies or that I understand your question...but generally you can check for the existence of a record in a different table several ways. One is:

Proc sql;

create table want as

select

t1.*

from

dataset1 t1

left outer join dataset2 t2

on t1.var1=t2.var1

where

t2.var1 is null;

quit;

Re: PROC FORMAT

PGStats — Fri, 15 Mar 2013 19:48:55 GMT

How about:

proc sql;

create table missing as

select * from dataset2

where var1 in (select var1 from dataset1 where upcase(var2)="MISSING");

quit;

Re: PROC FORMAT

robertrao — Fri, 15 Mar 2013 20:32:13 GMT

Hi ,

It was vwery helpful.Thanks for the replies...Could you also help me with creating CNTLIN...I am in the process of learning

Regards

Re: PROC FORMAT

PGStats — Fri, 15 Mar 2013 21:00:00 GMT

For the sake of demonstration ...

/* Create a CNTLIN dataset as a SAS view with the required variable names */

data missFormat / view=missFormat;
set dataset2;
FMTNAME = "missFormat";
START = var1;
LABEL = strip(var2);
run;

/* Create the format */

proc format cntlin=missFormat; run;

/* Use the format to subset data, for example */

data dataset3;
set dataset1(where=(put(var1,missFormat.)="missing"));
run;

Not that I would ever do this...