BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

Hi,

If I have a field called FSA, how do I check if it has a value (eg. M5V) missing , then if it is not ther populate it with the information from another field eg FSA = M1. If it's there, then do nothing.

HAVE:

FSA     Field1     Field2

M5         A           45

M5         B           45

M5        A            36

M7A       A           98

M7         A           76

......

I want to check if the table has field M5V, if it doesn't then this is what I expect:

FSA      Field1          Field2

M5V         A                45

M5V         B                45

M5V        A                 36

M5          A                 45

M5          B                 45

M5          A                 36

M7A        A                 98

...

same test would be for other FSa's, not just M5V..

thanks..

M7         A           76

3 REPLIES 3
AncaTilea
Pyrite | Level 9

Hi.

This is a poorly written code (since I find it a bit confusing at how you want to assign data if a certain value is missing):

*create data;

data have;

input FSA     $ Field1     $ Field2;

cards;

M5         A           45

M5         B           45

M5        A            36

M7A       A           98

M7         A           76

;

data test;

    set have;

    by fsa;

    output;

    flag = (fsa = "M5");

    if fsa ne "M5V" & flag = 1 then do;

        temp_fsa = "M5V";temp_field1 = field1;temp_field2 = field2;

    end;

    output;

run;

data want;

    set test;

    if temp_fsa ne "" then do;

        fsa = temp_fsa;

        field1 = temp_field1;

        field2 = temp_field2;

    end;

    drop temp: flag;

run;

proc sort data = want nodupkey;by fsa field1 field2;run;

This could be further improved, and possibly a macro could be used to allow for various FSA values...

ChrisNZ
Tourmaline | Level 20

How do you know value M5V should even exist if it is not in the file?

Ksharp
Super User

OK. If I understand what you mean.

data have;
input FSA     $ Field1     $ Field2;
cards;
M5         A           45
M5         B           45
M5        A            36
M7A       A           98
M7         A           76
;
run;
data want(drop=rc _:);
 if _n_ eq 1 then do;
  declare hash ha(dataset:'have(where=(fsa="M5"))',multidata:'y');
   ha.definekey('fsa');
   ha.definedata(all:'y');
   ha.definedone();
  declare hash h();
   h.definekey('fsa');
   h.definedone();
end;
set have;
_fsa=fsa;_field1=field1;_field2=field2;
 if h.add() eq 0 then do;
                        rc=ha.find();
                              do while(rc=0);
                               fsa=cats(fsa,'V');
                               output;
                               fsa=substr(fsa,1,length(fsa)-1);
                               rc=ha.find_next();
                              end;
                            end;
fsa=_fsa;field1=_field1;field2=_field2;
output;
run;

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 631 views
  • 0 likes
  • 4 in conversation