sjb1 Tracker

Many to many merge without ID

sjb1 — Mon, 25 Aug 2025 23:19:45 GMT

I have two datasets: A and B. I would like to merge them such that for each record in dataset A, every record in dataset B is matched and the values from A are retained until the next row of A is reached. A and B do not have an ID to match on.

See below for examples and desired results. I think the solution might require a set statement for dataset B inside a do loop with manual output control but I've been thwarted by the end of file processing.

Dataset A:

ID	Time	X1
1	1	100
1	2	1000
2	1	200
2	2	2500
2	3	300
3	1	100
3	2	500

Dataset B:

Horizon	Curve
1	100
2	90

Desired Dataset:

ID	Time	X1	Horizon	Curve
1	1	100	1	100
1	1	100	2	90
1	2	1000	1	100
1	2	1000	2	90
2	1	200	1	100
2	1	200	2	90
2	2	2500	1	100
2	2	2500	2	90
2	3	300	1	100
2	3	300	2	90
3	1	100	1	100
3	1	100	2	90
3	2	500	1	100
3	2	500	2	90

Re: Can you sort data using ORDER BY in Proc SQL so that it saves the sorted order in a table?

sjb1 — Thu, 14 Nov 2024 22:35:48 GMT

Yes! Your code will produce the outcome you are looking for. Dataset NEW will sorted by ID, DOB, SEX.

Re: Processing Time on Macro Variables Resolved within a Macro Function vs a Data-Step Function

sjb1 — Mon, 29 Apr 2024 21:12:52 GMT

You were right! It seems that the Data-Step Compiler does not recognize:

intnx("QUARTER", "&macro_date", &h, "E")

as a constant.

%macro macro_vs_datastep();

	options fullstimer;

	
	data input_dta;
		length Date t 8;

		do i = 1 to 100;
			Date = intnx("MONTH", "01JAN1980"d, i, "E");
			do t = 1 to 10000;
				output;
			end;
		end;
	run;	

	%let macro_date = 01JAN1980;
	%let months_to_process = 100;

	data data_step;
		set input_dta;
		%do h = 1 %to &months_to_process;
			if Date = intnx("MONTH", "&macro_date"d, &h, "E") then output;
		%end;
	run;

	data macro;
		set input_dta;
		%do h = 1 %to &months_to_process;
			if Date = %sysfunc(intnx(MONTH, "&macro_date"d, &h, E)) then output;
		%end;
	run;

%mend;

%macro_vs_datastep();

Here's the log:

NOTE: There were 1000000 observations read from the data set WORK.INPUT_DTA.
NOTE: The data set WORK.DATA_STEP has 1000000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 18.73 seconds
user cpu time 18.70 seconds
system cpu time 0.03 seconds
memory 1497.43k
OS Memory 21272.00k
Timestamp 04/29/2024 05:05:39 PM
Step Count 59 Switch Count 9

NOTE: There were 1000000 observations read from the data set WORK.INPUT_DTA.
NOTE: The data set WORK.MACRO has 1000000 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.23 seconds
user cpu time 0.21 seconds
system cpu time 0.01 seconds
memory 1195.31k
OS Memory 21088.00k
Timestamp 04/29/2024 05:05:40 PM
Step Count 60 Switch Count 5

This could be specific to intnx(), other functions might be optimized to recognize this behavior, but good to know!

Processing Time on Macro Variables Resolved within a Macro Function vs a Data-Step Function

sjb1 — Mon, 29 Apr 2024 20:21:44 GMT

I am wondering if there are any rules about how SAS handles constants and if having the macro language resolve macro variables before they reach the data-step can improve processing time.

Take the following toy example. (I know there are more efficient ways to achieve the same outcome in the code below, this is purely for illustrative purposes.)

%macro macro_vs_datastep();

	%let macro_date = 01JAN1980;
	%let quarters_to_process = 10;

	data data_step;
		set sashelp.gnp;
		%do h = 1 %to &quarters_to_process;
			if intnx("QUARTER", Date, 0, "E") = intnx("QUARTER", "&macro_date"d, &h, "E") then output;
		%end;
	run;

	data macro;
		set sashelp.gnp;

		%do h = 1 %to &quarters_to_process;
			if intnx("QUARTER", Date, 0, "E") = %sysfunc(intnx(QUARTER, "&macro_date"d, &h, E)) then output;
		%end;
	run;

%mend;

%macro_vs_datastep();

My intuition is that that resolving the macro variables 'macro_date' and 'h' inside of %sysfunc() should reduce I/O time as in the 2nd data-step, as SAS only has to resolve intnx() with each loop of 'h'. Compare that to the first data-step where intnx() must be resolved with each loop of 'h' along with each record in the dataset.

Are there any rules-of-thumb around best practices in these sorts of situations?

Re: Controlling PDF Bookmark text style attributes

sjb1 — Fri, 17 Dec 2021 18:48:21 GMT

Thanks for the resources, but this is not quite what I'm looking for. I want to control the attributes of the PDF bookmarks. For instance, I want to make some bold.

I've attached a picture of what I want. I can make the text bold using a PDF editing tool, but I'm curious if SAS has this capability.

Controlling PDF Bookmark text style attributes

sjb1 — Tue, 07 Dec 2021 15:05:30 GMT

Hello,

Is there any way to control the style attributes of the text of a PDF bookmark produced by SAS?

I've posted a trivial example that hopefully gets at what I want to do, please let me know if a full example would be helpful.

ods escapechar="^";

PROC DOCUMENT name=MyDoc;
    make \MyDoc\First_Level;
    setlabel \MyDoc\First_Level "^{style [font_weight=bold] Custom Text}"; /* Define the PDF bookmark label and attempt to make it Bold */
    /* place output here */
    doc close;
RUN;
QUIT;

ods pdf file="MyPDF.pdf";
PROC DOCUMENT name=MyDoc;
    replay;
RUN;
QUIT;
ods pdf close;

Thanks!

Re: PROC DOCUMENT replay does not preserve dattrmap

sjb1 — Mon, 20 Sep 2021 16:07:16 GMT

Hi Dan,

I was hoping to avoid embedding the attribute within the GTL itself as the rest of my workflow references the attribute maps. Anyways, thank you for your insight.

PROC DOCUMENT replay does not preserve dattrmap

sjb1 — Sun, 19 Sep 2021 18:58:31 GMT

I have found an issue when replaying the output from PROC SGRENDER with a user defined template with a discrete attribute map, the replay called by PROC DOCUMENT does not preserve the attributes contained in the dattrmap. Is there a solution to this or is this a bug?

Here is an example of the behavior:

/* Create the attribute map data set */
data attrmap;
  length ID VALUE MARKERCOLOR MARKERSYMBOL LINECOLOR LINEPATTERN $15;
  input ID$ VALUE$ MARKERCOLOR$ MARKERSYMBOL$ LINECOLOR$ LINEPATTERN$;
datalines;
stockname IBM       red   circlefilled   red   solid
stockname Intel     green trianglefilled green shortdash
stockname Microsoft blue  squarefilled   blue  dot
;
run;

/* Create a stock data set for the year 2002 */
proc sort data=sashelp.stocks out=stocks;
  by stock date;
  where date between '01JAN02'd and '30DEC02'd;
run;

/* Create a template for IBM, Microsoft, and Intel stocks */
proc template;
define statgraph stockchart;
begingraph;
  entrytitle "Trends for IBM, Intel, and Microsoft";
  discreteattrvar attrvar=stockmarkers var=stock
    attrmap="stockname";
  layout overlay;
    seriesplot x=date y=close /
      group=stockmarkers
      display=(markers)
      name="trends";
    discretelegend "trends" / title="Stock Trends";
  endlayout;
endgraph;
end;
run;

ods document name=work.test;
/* Plot the stock trends */
proc sgrender data=stocks dattrmap=attrmap template=stockchart;
run;
ods document close;

/* replay PROC SGRENDER output */
proc document name=work.test;
	replay;
run;
quit;

The output of the above code is below. Clearly, the output from the PROC DOCUMENT replay is missing the attributes contained in work.attrmap . Any help on this would be appreciated.

PROC SGRENDER outputPROC DOCUMENT Replay output