Case statement with multiple variables

[maricelj]

Hello, I am a newbie here, I need your help. Thank you in advance.

My data goes like this:
Data: Test
Name Trans_Month File_Attach
A 01 QQQ
A 02 BBB
A 04 DDD
B 01 DDD
B 02 YYY
B 05 GGG

This is the Output i am looking for:
[NOTE: Trans_Month=01 to 12, if no file_attach on a specific trans_month then file_attach=X]
Name 01 02 03 04 05 ....
A QQQ BBB X DDD X
B DDD YYY X X GGG

I used case statement but the output goes like this:
Name 01 02 03 04 05 .....
A QQQ
A ______BBB
A ___________X
A____________ DDD
A _________________X
B DDD
B _____YYY
B ___________X
B _______________X
B __________________GGG

(please disregard the line (____), treat it as blank space)
What shall i do to place the output within the same row? Thank you so much.

[ChrisNZ]

Something like this should help.

data HAVE;
 input NAME $ TRANS_MONTH $ FILE_ATTACH $;
 cards;
A 01 QQQ
A 02 BBB
A 04 DDD
B 01 DDD
B 02 YYY
B 05 GGG
;
proc transpose data=HAVE out=TRANS prefix=MTH;
  by NAME;  
  id TRANS_MONTH;  
  var FILE_ATTACH;
run;
data WANT;
  array M [12] $8 MTH01 - MTH12 ;
  set TRANS ;
  do I=1 to 12;
    if missing(M[I]) then M[I]='X';
  end;
run;

Please adapt to your needs.

Obs	NAME	MTH01	MTH02	MTH03	MTH04	MTH05	MTH06	MTH07	MTH08	MTH09	MTH10	MTH11	MTH12
1	A	QQQ	BBB	X	DDD	X	X	X	X	X	X	X	X
2	B	DDD	YYY	X	X	GGG	X	X	X	X	X	X	X

[maricelj]

Hi,
Thank you for this.
Will try to give you feedback as soon as possible.
Keep safe and have a great day ahead.

[ChrisNZ]

No worries. Note the code used to create table HAVE. That's how you should provide data.  A table is not useful to recreate your problem.

[maricelj]

Hi ChrisNZ,

Thank you for the help.

May I just request another thing. Sorry I am really a newbie here.

The code is working, it's just that I am having trouble for some months with duplicates and are under the same name.

How can I eliminate those duplicates based on file attachments? *The latest date will be retained.

The attachment is concatenated with filename, month, day and time.

Something like this:

data HAVE;
 input NAME $ TRANS_MONTH $ FILE_ATTACH $;
 cards;
A 01 QQQFEB2207:54
A 01 QQQFEB2002:54
A 04 DDDMAR0112:01
B 01 DDDJAN1807:00
B 05 YYYSEP0111:10
B 05 GGGOCT1108:21
;

The output should be something like this:
Obs NAME MTH01         MTH02 MTH03     MTH04     MTH05 MTH06 MTH07 MTH08 MTH09 MTH10 MTH11 MTH12 
1    A   QQQFEB2207:54 X      X     DDDMAR0112:01  X    X     X      X     X     X      X     X 
2    B   DDDJAN1807:00 X      X        X       GGG1108:21 X    X     X     X     X      X     X
Thank you so much and have a great day ahead.

maricelj

[ChrisNZ · Accepted solution]

You need to sort the source data by date and keep the last observation.

data HAVE(index=(A=(NAME TRANS_MONTH DATE)));
 input NAME $ TRANS_MONTH $ FILE_ATTACH $;
 DATE=input(substr(FILE_ATTACH,4),monyy5.);
 cards;
A 01 QQQFEB2207:54
A 01 QQQFEB2002:54
A 04 DDDMAR0112:01
B 01 DDDJAN1807:00
B 05 YYYSEP0111:10
B 05 GGGOCT1108:21
;
data FILTERED;
  set HAVE;
  by NAME TRANS_MONTH DATE;
  if last.TRANS_MONTH;
run;

[maricelj]

CrizNZ,

Thank you so much. It works.

Have a great day and keep safe.