weird behavior of SAS need to explain

[himanshu1]

OK let me explain my problem what i want to do is i have file which consist of record for example :-

011234567412312mancbhansmamFTWP
012314263219665haiaananaajaFTWM
011236478748969jakakakancndFTSI
021234567412312sasasasasasasasa
021234567412312sasasasasasasasa
021236478748969asasasasasasasaa

now what i want to do is , i want to take only record starting with 01 and after that i want to sort it last 4 bytes of these record

after sorting i want to merge my data back with 01 and 02 records in below way

011236478748969jakakakancndFTSI
021236478748969asasasasasasasaa
012314263219665haiaananaajaFTWM
011234567412312mancbhansmamFTWP
021234567412312sasasasasasasasa
021234567412312sasasasasasasasa

i have used below code :- 

data abc;
input @001 key $2.
@003 app_id $13.
@016 id $12.
@028 ftype $4.;
datalines;
011234567412312mancbhansmamFTWP
012314263219665haiaananaajaFTWM
011236478748969jakakakancndFTSI
;
run;

proc sort data=abc;
by ftype app_id;

proc print data=abc;
run;

data _null_;
call symputx("nob",nobs);
set abc nobs=nobs;
call symputx(cats("a",_N_),app_id);
run;

%put &nob;

data abc1;
input @001 key $2.
@003 app_id $13.
@016 id $12.
@028 ftype $4.;
datalines;
011234567412312mancbhansmamFTWP
012314263219665haiaananaajaFTWM
011236478748969jakakakancndFTSI
021234567412312sasasasasasasasa
021234567412312sasasasasasasasa
021236478748969asasasasasasasaa
;
run;

%macro abc12;
data abc2;
%do i=1 %to &nob;
b=&&a&i;
%put &&a&i;
set abc1;
putlog "app_id" app_id;
if b eq app_id then do;
putlog "check" app_id;
output abc2;
end;
%end;
%mend abc12;

%abc12;

but I'm getting this as output :-

011234567412312mancbhansmamFTWP
012314263219665haiaananaajaFTWM
011236478748969jakakakancndFTSI
021234567412312sasasasasasasasa
021234567412312sasasasasasasasa
021236478748969asasasasasasasaa

 I want explanation for this behavior also Thanks in advance

[Tom]

Why are you using macro code? What CODE is it that you need to generate?  Your problem descriptions makes it sound like you want to manipulate DATA.

Why not just read the values into datasets and manipulate the datasets.  Is there some reason why you need to create new lines of text?

[himanshu1]

Basically what i want to do is i want to sort the data based on some record and after that i want to add the remaining rows in between then them (According to 2 field in relation)

i have data like

01 1234567412312 mancbhansmam FTWP
01 2314263219665 haiaananaaja FTWM
01 1236478748969 jakakakancnd FTSI
02 1234567412312 sasasasasasa sasa
02 1234567412312 sasasasasasa sasa
02 1236478748969 asasasasasas asaa

now what i want to do is:-  first i want to extract observation based on first two bytes. if it is "01" then extract it:-

01 1234567412312 mancbhansmam FTWP
01 2314263219665 haiaananaaja FTWM
01 1236478748969 jakakakancnd FTSI 

after that sort the data on basis of last for bytes :-

01 1236478748969 jakakakancnd FTSI
01 2314263219665 haiaananaaja FTWM
01 1234567412312 mancbhansmam FTWP

after re-merge the data so that order remain the same "FTSI" come first then all "02" record related to it based on 2 field

for "FTSI" 

2nd field is :- 1236478748969

and its related "02" record is

02 1236478748969 asasasasasas asaa which have this "1236478748969" in common

01 1236478748969 jakakakancnd FTSI
02 1236478748969 asasasasasas asaa
01 2314263219665 haiaananaaja FTWM
01 1234567412312 mancbhansmam FTWP
02 1234567412312 sasasasasasa sasa
02 1234567412312 sasasasasasa sasa

[Tom]

What does "sort based on some record mean"?  You sort records based on the values of the fields.  You sort observations based on the values of the variables.

What does "add remaining rows" mean?   Where are these other rows coming from? 

I suspect that you need to define a new variable that will let your group the data, but I cannot figure out from your description how you are deciding that.

[Tom]

I think I might have a hint of an idea what the heck you are talking about.

It looks like want somehow to merge the value of FTYPE from and KEY=01 record onto the related KEY=02 records. 

Let's call this new variable FTYPE01.  Then you could use that new variable in your sort key.

The logic of how you are matching the records is beyond me.

KEY  APPL_ID  ID FTYPE FTYPE01 
01 1236478748969 jakakakancnd FTSI FTSI
02 1236478748969 asasasasasas asaa FTSI
01 2314263219665 haiaananaaja FTWM FTWM
01 1234567412312 mancbhansmam FTWP FTWP
02 1234567412312 sasasasasasa sasa FTWP
02 1234567412312 sasasasasasa sasa FTWP

[himanshu1]

/*a1 :- 1236478748969*/ 
/*a2 :- 2314263219665*/
/*a3 :- 1234567412312*/

/* abc1 

011234567412312mancbhansmamFTWP
012314263219665haiaananaajaFTWM
011236478748969jakakakancndFTSI
021234567412312sasasasasasasasa
021234567412312sasasasasasasasa
021236478748969asasasasasasasaa

app_id:-3 byte to 15 byte
*/

%macro abc12;
data abc2;
%do i=1 %to &nob;
b=&&a&i;
%put &&a&i;
set abc1;
putlog "app_id" app_id;
if b eq app_id then do;
putlog "check" app_id;
output abc2;
end;
%end;
%mend abc12;

%abc12;

/* according to my code if firstly b=a1
a1 is matching with 3 observation of abc1
so it should print out it first then it 
a1 is also matching with 6th observation of abc1
after that b=a2 then it should print 2nd observation of abc1
after that b=a3 then it should print 1st observation 4th
observation and 5th observation of abc1 but it is not printing*/ 

[Tom · Accepted solution]

I think you want to combine the records based on APP_ID?

Here is your test data.

data have;
 input key $ 1-2 app_id $ 3-15 id $16-27 ftype $ 28-31 ;
list;
datalines;
011234567412312mancbhansmamFTWP
012314263219665haiaananaajaFTWM
011236478748969jakakakancndFTSI
021234567412312sasasasasasasasa
021234567412312sasasasasasasasa
021236478748969asasasasasasasaa
;

We could use SQL to join them.  

proc sql ;
 create table want as
  select a.*,b.ftype as ftype01
  from have a left join have b
  on b.key='01'
  and a.app_id = b.app_id
  order by ftype01,a.app_id,a.key
 ;
quit;

Results:

Obs    key       app_id             id         ftype    ftype01

 1     01     1236478748969    jakakakancnd    FTSI      FTSI
 2     02     1236478748969    asasasasasas    asaa      FTSI
 3     01     2314263219665    haiaananaaja    FTWM      FTWM
 4     01     1234567412312    mancbhansmam    FTWP      FTWP
 5     02     1234567412312    sasasasasasa    sasa      FTWP
 6     02     1234567412312    sasasasasasa    sasa      FTWP

You could also just use normal SAS code instead of SQL code.

proc sort data=have ;
  by app_id key ;
run;
data want ;
  set have ;
  by app_id ;
  if first.app_id then ftype01=ftype;
  retain ftype01;
run;
proc sort;
  by ftype01 app_id key ;
run;

[Tom]

Are you just asking why the capital letters sort before the lowercase letters?  That is how ASCII codes work.