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
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;
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?
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
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.
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
/*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*/
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;
Are you just asking why the capital letters sort before the lowercase letters? That is how ASCII codes work.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.