SAS data query

[pdhokriya]

Hi Reader,

Below are some requirement.

1. Need to create new variables cmclscd02 to cmclscd31.

2. When sts = "A" and CMCLASCD = cd_a (multiple record, atleast 1 value should match ) then data should be CMCLASCD , cmclscd02, cmclscd03..etc.

3. When sts = "A" and CMCLASCD = cd_a CMCLASCD = cd_a (one record) then data should be CMCLASCD

4. When cmclscd ne cd_a then consider sts = "I" data then consider other dataset variable = cd_i and data should be collect in CMCLASCD , cmclscd02, cmclscd03..etc.

5. Output data is mentioned below for your reference.

data xyz;

input name $  CMCLASCD $  CD_a $ /*CD_i $ 18-22*/ STS $ ;

cards;

1003 B01AC A01AD A

1003 B01AC B01AC A

1003 B01AC M02AC A

1003 B01AC N02BA I

1003 C08DB C05AE A

1003 C08DB C08DB A

1003 G04CA G04CA A

1003 R03AC R03AC A

1003 R03AC R03CC A

1003 R03AC R03CH A

1003 R03BA D07AC A

1003 R03BA R01AD A

1003 R03BA R03BA A

1003 R03BA R03BA A

1003 R03BA R03BO A

1003 R03BA R03BF A

1003 R03BA R03BT A

1003 R03BA R03BR A

1003 R03BA R03BK A

1003 R03BA R03BP A

1003 R03BA R03BD A

1003 R03BE R03BE A

1003 R03BZ R03BH I

1003 R03BZ R03BQ I

;

run;

Output data:

NAME

CMCLASCD

CMCLSC01

CMCLSC02

CMCLSC03

CMCLSC04

CMCLSC05

CMCLSC06

CMCLSC07

CMCLSC08

CMCLSC09

CMCLSC10

CMCLSC11

CMCLSC12

CMCLSC13

CMCLSC14

CMCLSC15

CMCLSC16

CMCLSC17

CMCLSC18

CMCLSC19

CMCLSC20

CMCLSC21

CMCLSC22

CMCLSC23

CMCLSC24

CMCLSC25

CMCLSC26

CMCLSC27

CMCLSC28

CMCLSC29

CMCLSC30

CMCLSC31

1003

B01AC

A01AD

M02AC

1003

C08DB

C05AE

1003

G04CA

1003

R03AC

R03CC

R03CH

1003

R03BA

D07AC

R01AD

R03BA

R03BO

R03BF

R03BT

R03BR

R03BK

R03BP

R03BD

1003

R03BE

1003

R03BZ

R03BH

R03BQ

[Kurt_Bremser]

Please repost your expected result as pdf or xlsx. The online previewer has problems with older Excel formats.

[pdhokriya]

Share in massage

NAME CMCLASCD CMCLSC01 CMCLSC02 CMCLSC03 CMCLSC04 CMCLSC05 CMCLSC06 CMCLSC07 CMCLSC08 CMCLSC09 CMCLSC10 CMCLSC11 CMCLSC12 CMCLSC13 CMCLSC14 CMCLSC15 CMCLSC16 CMCLSC17 CMCLSC18 CMCLSC19 CMCLSC20 CMCLSC21 CMCLSC22 CMCLSC23 CMCLSC24 CMCLSC25 CMCLSC26 CMCLSC27 CMCLSC28 CMCLSC29 CMCLSC30 CMCLSC31
1003 B01AC A01AD M02AC N02BA
1003 C08DB C05AE
1003 G04CA
1003 R03AC R03CC R03CH
1003 R03BA D07AC R01AD R03BA R03BO R03BF R03BT R03BR R03BK R03BP R03BD
1003 R03BE
1003 R03BZ R03BH R03BQ

[pdhokriya]

Posted in my question, please look at it. Thank you in advance. Take care.

[pdhokriya]

Hi,

Could you please help me out from this problem.

Regards
Priyanka

[Kurt_Bremser]

You discard

1003 B01AC N02BA I

but keep

1003 R03BZ R03BH I
1003 R03BZ R03BQ I

What is the difference between those observations?

[pdhokriya]

if data is matched i.e.(CMCLASCD = CD_a) then will consider only "A" values. That means will exclude I valued.

1003 B01AC A01AD A
1003 B01AC B01AC A
1003 B01AC M02AC A
1003 B01AC N02BA I

I data does not match i.e.(CMCLASCD ne CD_a) then will consider I values only not A.

1003 R03BZ A
1003 R03BZ R03BH I
1003 R03BZ R03BQ I

Hope I have cleared this. If any thing else plz do let me know.

[Kurt_Bremser · Accepted solution]

So, "match" in your parlance actually means "group".

This code creates exactly your output as wanted:

data have;
input name $  CMCLASCD $  CD_a $ /*CD_i $ 18-22*/ STS $ ;
cards;
1003 B01AC A01AD A
1003 B01AC B01AC A
1003 B01AC M02AC A
1003 B01AC N02BA I
1003 C08DB C05AE A
1003 C08DB C08DB A
1003 G04CA G04CA A
1003 R03AC R03AC A
1003 R03AC R03CC A
1003 R03AC R03CH A
1003 R03BA D07AC A
1003 R03BA R01AD A
1003 R03BA R03BA A
1003 R03BA R03BA A
1003 R03BA R03BO A
1003 R03BA R03BF A
1003 R03BA R03BT A
1003 R03BA R03BR A
1003 R03BA R03BK A
1003 R03BA R03BP A
1003 R03BA R03BD A
1003 R03BE R03BE A
1003 R03BZ R03BH I
1003 R03BZ R03BQ I
;

data want;
merge
  have
  have (
    in=check
    drop=sts
    rename=cd_a=_cd_a
    where=(_cd_a = cmclascd)
  )
;
by name cmclascd;
array cmclsc {*} cmclsc01-cmclsc10 _character_;
retain cmclsc01-cmclsc10;
if first.cmclascd
then do;
  do count = 1 to 10;
    cmclsc{count} = "";
  end;
  count = 0;
end;
if (not check or sts = "A") and cd_a ne cmclascd
then do;
  count + 1;
  cmclsc{count} = cd_a;
end;
if last.cmclascd;
drop cd_a _cd_a count sts;
run;

[pdhokriya]

My approch was this, but I really liked your program. Thank you so much for your help.
Take care.


Data match;
set xyz;
Match = 'Y';
where CMCLASCD = cd_a and STS = "A";
Run;

DAta match1;
merge xyz match (keep= name CMCLASCD match);
by name CMCLASCD;
if match = "Y" and STS = "A";
run;
proc sql;
Create table match_n as select *, count(*) as cnt from match1
group by cmclascd;
quit;

Data match_n;
set match_n;
if CMCLASCD = cd_a and cnt gt 1 then delete;
run;


Proc transpose data= match_n out= final1;* prefix= CMCLSC0;
by name CMCLASCD;
Var cd_a ;
run;

Data final_a;
set final1;
drop _name_ ;
Run;
**************************;
data final2;
set xyz (where = (sts = "I"));
Run;

Proc transpose data= final2 out= final2_i(drop=_name_);
by name CMCLASCD;
Var cd_a ;
run;

Data final;
set final_a final2_i;
Run;
****************;

Proc sql noprint;
Select count(varnum) into: num_vars from sashelp.vcolumn
where libname = 'WORK' and memname = "FINAL";
quit;

Data _nul_;
set sashelp.vcolumn;
where libname = 'WORK' and memname = "FINAL";
call symput(cat('a', _n_), Name);
call symput(cat('b', _n_),compress('CMCLSC'||put(_N_-2, z2.)));
Run;

%macro rename();
Data final_all;
set final;
%do i = 1 %to &num_vars.;
%if &i. gt 2 %then %do;
&&b&i. = &&a&i.;
drop &&a&i.; %end;
%end;
run;
%mend;

%rename();