topic Re: Finding values and creating datasets in SAS Programming

Finding values and creating datasets

Ranjeeta — Thu, 24 Jan 2019 16:49:44 GMT

In the attached file you will see a variable form which I need to be able to create three datasets

1st dataset(Missing) : find values that contain missing values in any of the 23 characters of the variable SP440

2nd dataset(Unknown) find values that contain 8 in the 1st character or 9 in any of the total 23 characters

3rd dataset (Invalid entries): find values that contain any value other than P,Y, N or 8 in the 1st character

Please advise how would I write a code to create these three datasets

Regards

Re: Finding values and creating datasets

novinosrin — Thu, 24 Jan 2019 17:01:53 GMT

data missing unknown invalid;

set have;

if anyspace(SP440) then output missing;
else if first(SP440)='8' or  index(SP440,'9')>0 then output unknown;

else if first(SP440) not in ('P','Y','N','8') then output invalid;

run;

Re: Finding values and creating datasets

Ranjeeta — Thu, 24 Jan 2019 17:04:01 GMT

Thankyou for your quick reply really appreciate it will try it out
Regards

Re: Finding values and creating datasets

AMSAS — Thu, 24 Jan 2019 17:10:40 GMT

Here's another way to do it. Note I create another dataset "other" which contains observations that would not go into the other 3 datasets.

data input ;
	input sp440 $23. ;
cards;
Y03270745270816Y270858Y
Y03180759180957Y181033Y
Y04090027090253Y090314Y
Y04100841100926Y101003Y
Y04170832170903Y170931Y
P        172043Y172100Y
P        232235Y232256Y
P        262212Y262244Y
P        282020Y282046Y
Y04292040292109Y292125Y
Y04300524300559Y300638Y
Y04292358300122Y300139Y
Y05131507131627Y131745Y
Y05160528160600Y160619Y
Z05151244151410N      N
I05221247221314Y221332Y
Y05201954202145Y202204Y
Y05211103211138Y211156Y
Y06051414051446Y051520Y
Y06082303090126Y090142Y
run ;

data missing unknown invalid other;
	set input ;
	if substr(sp440,1,1) not in ("P","Y","N","8") then 
		output invalid ;
	else if substr(sp440,1,1) = "8" or find(sp440,"9") then
		output unknown ;
	else if find(sp440," ") then
		output missing ;
	else
		output other ;
run ;

Re: Finding values and creating datasets

novinosrin — Thu, 24 Jan 2019 17:17:24 GMT

Hi @AMSAS if you are using an extract function but extracting no more than 1 byte of length

if substr(sp440,1,1)

can be replaced by

if char(sp440,1)

Imho, substr is at best when length to extract exceeds 1 to n bytes