finans_sas Tracker

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Thu, 21 Sep 2023 20:42:32 GMT

Hi @Quentin ! I actually ended up running the code three times (4 corvars at a time) since the computer kept running out of disk space. If I recall correctly, it was 20 billion or so for each run.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Thu, 21 Sep 2023 20:26:54 GMT

Thank you so much for this code, @Tom ! I will explore it further.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Thu, 21 Sep 2023 20:19:46 GMT

You are absolutely right, @PaigeMiller. Next time If I have another question, I will make sure to include all the important details. I sincerely apologize for this. Thank you so much for all your help and the code that your proposed. It worked like a charm.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Thu, 21 Sep 2023 00:20:53 GMT

Great question, @PaigeMiller ! This is related to the need to show that the results are robust to different ways of measuring the variable of interest and to the different subsamples. Most of them will be a footnote anyways but we need to perform them.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Thu, 21 Sep 2023 00:15:14 GMT

Thank you so much for sharing this other perspective, @Reeza . I need to think about how it will work with the data. The starting dataset has around 50 million rows of data (another one that I need to apply the same code to has about 250 million).

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 20:26:16 GMT

Quick clarification: by "0 or 1" and "0,1" I meant that we include all the values where the variable is either 0 or 1, which by definition is all the observations for a given dummy variable.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 20:23:11 GMT

Thank you @Reeza for this question. Your statement is correct. The combinations will 0, 1, and 0&1 together versus 0, 1, and 0&1 together. I have 12 variables (corvar1 through corvar12), and each corvar takes the values of 0 and 1. And the other dummy, allmdummy, takes the values of 0 and 1 too. I want to be able to run the same regression for every single combination, which amounts to 108. All the possible combinations for the first variable will look like the following:

corvar1=0 and allmdummy=0,

corvar1=0 and allmdummy=1

corvar1=0 and allmdummy= 0 or 1

corvar1=1 and allmdummy=0,

corvar1=1 and allmdummy=1

corvar1=1 and allmdummy= 0 or 1

corvar1=0,1, allmdummy=0,

corvar1=0,1 allmdummy=1

corvar1=0,1 allmdummy= 0 or 1

Ideally, I will be able to create a unique sampleid for each combination and then run proc reg and proc means command for each combination (sampleid). I hope this helps. Please let me know if you have any follow-up questions.

Thank you so much!

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:47:33 GMT

Thank you so much for your suggestions, @Quentin . If I could find a way to do the proc reg without splitting the dataset, that will actually be awesome since the splitting and re-combining will take a massive amount of hard disk space. The challenge I have at this point is that some observations will fall under more than one sample ID due to overlapping subsets.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:36:37 GMT

Thank you so much once again for your quickly reply, @PaigeMiller . Here is my challenge: the subsets will be overlapping. For example, sampleid 111 will be a combination of 112 and 113. I could not figure out how to create sample IDs without overwriting the existing ones.

Re: SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:24:43 GMT

Thank you so much, @PaigeMiller for your prompt reply. You made an excellent point. It is a nice segway into my end goal. I actually want to run the following for each sampleid (assuming that wanted is the output of the prior macro). If I could find a way to incorporate the prior macro into the next one, that will indeed save me a lot of time. I will really appreciate any insight on this one.

proc reg data=wanted noprint outest=wanted2 noprint adjrsq;
by sample id obsid; /*obsid is another ID already stored in the dataset*/
model depvar1 = ind1 ind2 ind3 ind4 / hcc;
run;

proc means data=wanted2;

by sampleid;
var intercept ind1 ind2 ind3 ind4 _rsq_ _adjrsq_;
output out=wanted3 mean = std= t= probt= / autoname;
run;

SAS Macro & Nested Do Loop to subset data

finans_sas — Wed, 20 Sep 2023 14:02:00 GMT

Hello Everyone:

I have a big dataset, and I want to create subsets from this dataset and combine all the subsets into a separate dataset. Here are more details:

corvar1 through corvar12: 12 different dummy variables. Each variable takes the value of 0 or 1.

allmdummy: a dummy variable that takes the value of 0 or 1.

I want to create 108 different subsets. For example, for corvar1, I would like to create the following nine subsets:

corvar1 le 1 + allmdummy le 1 (include all values of corvar1 and all values of allmdummy)

corvar1 le 1 + allmdummy eq 1

corvar1 le 1 + allmdummy lt 1

corvar1 eq 1 + allmdummy le 1

corvar1 eq 1 + allmdummy eq 1

corvar1 eq 1 + allmdummy lt 1

corvar1 lt 1 + allmdummy le 1

corvar1 lt 1 + allmdummy eq 1

corvar1 lt 1 + allmdummy lt 1 (include only the values where corvar1=0 and allmdummy=0).

Below is a rough sketch of what I would like to accomplish. I would love to have your help with improving this code.

Thank you so much!

%macro groupa;
%do cvar=1 %to 12; /*names for the variables*/
%do m=1 %to 3; /*for math operations*/
%do n=1 %to 3; /*for math operations*/

data set&cvar&m&n; set regsample;
if &m=1 then opa="le";
if &m=2 then opa="eq";
if &m=3 then opa="lt";

if &n=1 then opb="le";
if &n=2 then opb="eq";
if &n=3 then opb="lt";

if corvar&cvar opa 1 and allmdummy opb 1;
id1=&cvar;
id2=&m;
id3=&n;
sampleid=&cvar&m&n; /*create a sample ID*/
proc append base=wanted data=set&cvar&m&n; /*I want to combine all the datasets into a single one. Not sure if this syntax is right*/
%end;

%end;

%end;
%mend groupa;
%groupa

Re: Expanding the data set for each event day

finans_sas — Sun, 28 May 2023 09:53:58 GMT

Thank you so much @ballardw , @Patrick , and @mkeintz for helping me with my question. I sincerely appreciated all your help.

Have a wonderful weekend!

Re: Expanding the data set for each event day

finans_sas — Sat, 27 May 2023 00:24:04 GMT

Thank you so much @Patrick for this suggestion. When I ran it, it did not quite produce the dataset I would like to have. day_count starts for the first observation in the sample and goes all the way till the end of the sample instead of starting at each secid & eventday=1 pairs. Should I create an ID that combines secid and event day to accomplish this task?

Thank you so much

Re: Expanding the data set for each event day

finans_sas — Fri, 26 May 2023 22:42:49 GMT

Thank you so much @ballardw and @mkeintz for helping me with my inquiry. I really appreciated it. My data set extremely large, so perhaps the data step solution may work better for me. Please accept my apologies. The data set does have IDs, and I need to ensure that these steps are executed within the same IDs only. I did not work with hash operators before. How should I go about bringing in the IDs? I attempted below (bold line), but ended up getting something different.

Also: I opted for 180 days instead of 6 months since I need to have 180 observations for each event, and the 6-month cutoff may not necessarily produce 180 observations. If for some events, I have fewer than 180 observations (due to data limitations), this will be fine. I will first run the estimations with only those with 180 observations first, and later I will include all of them (even those with fewer than 180 observations).

data have;;
input secid obs_date :mmddyy10. event value;
format obs_date mmddyy10.;
datalines;
1 1/1/2020 0 0.003918297
1 1/2/2020 0 0.294439369
1 1/4/2020 1 0.048466828
1 1/5/2020 0 0.718177034
1 1/6/2020 0 0.001364211
1 1/7/2020 0 0.440971903
1 1/8/2020 0 0.55962246
1 1/9/2020 0 0.821105414
1 1/10/2020 1 0.332539126
1 1/11/2020 0 0.289808782
1 1/12/2020 0 0.911322784
1 1/18/2020 0 0.896737429
1 1/19/2020 0 0.333353538
1 1/20/2020 0 0.19605047
1 1/21/2020 0 0.60689124
1 1/22/2020 1 0.439833751
1 1/23/2020 0 0.896841702
1 1/24/2020 0 0.147458515
1 1/25/2020 0 0.923237859
1 1/26/2020 0 0.394894147
1 1/27/2020 0 0.935664763
1 1/28/2020 0 0.667981258
1 1/29/2020 1 0.872639458
1 1/30/2020 0 0.064826005
1 1/31/2020 0 0.109456422
1 2/1/2020 0 0.386348541
1 2/2/2020 0 0.732321068
1 2/3/2020 0 0.503449099
2 1/1/2020 0 0.003918297
2 1/2/2020 0 0.294439369
2 1/4/2020 1 0.048466828
2 1/5/2020 0 0.718177034
2 1/6/2020 0 0.001364211
2 1/7/2020 0 0.440971903
2 1/8/2020 0 0.55962246
2 1/9/2020 0 0.821105414
2 1/10/2020 1 0.332539126
2 1/11/2020 0 0.289808782
2 1/12/2020 0 0.911322784
2 1/18/2020 0 0.896737429
2 1/19/2020 0 0.333353538
2 1/20/2020 0 0.19605047
2 1/21/2020 0 0.60689124
2 1/22/2020 1 0.439833751
2 1/23/2020 0 0.896841702
2 1/24/2020 0 0.147458515
2 1/25/2020 0 0.923237859
2 1/26/2020 0 0.394894147
2 1/27/2020 0 0.935664763
2 1/28/2020 0 0.667981258
2 1/29/2020 1 0.872639458
2 1/30/2020 0 0.064826005
2 1/31/2020 0 0.109456422
2 2/1/2020 0 0.386348541
2 2/2/2020 0 0.732321068
2 2/3/2020 0 0.503449099
2 2/4/2020 0 0.60689124
2 2/5/2020 0 0.439833751
2 2/6/2020 0 0.896841702
2 2/7/2020 0 0.147458515
2 2/8/2020 0 0.923237859
;

data want (drop=event);
set have (where=(event=1));
by secid;
if _n_=1 then do;
declare hash h (dataset:'have (drop=event)',ordered:'a');
h.definekey('obs_date');
h.definedata(all:'Y');
h.definedone();
declare hiter hi ('h');
h.output(dataset:'h');
end;

day=0;
eventday=obs_date;
format eventday date9.;
output;
hi.setcur(); /*Point hash iterator at hash dataitem for event date */
do day=1 to 180 while (hi.next()=0);
output;
end;
run;

Expanding the data set for each event day

finans_sas — Fri, 26 May 2023 20:52:33 GMT

Hello Everyone:

I have a dataset similar to the one that I provided below:

obs_date	event	value
1/1/2020	0	0.003918
1/2/2020	0	0.294439
1/4/2020	1	0.048467
1/5/2020	0	0.718177
1/6/2020	0	0.001364
1/7/2020	0	0.440972
1/8/2020	0	0.559622
1/9/2020	0	0.821105
1/10/2020	1	0.332539
1/11/2020	0	0.289809
1/12/2020	0	0.911323
1/18/2020	0	0.896737
1/19/2020	0	0.333354
1/20/2020	0	0.19605
1/21/2020	0	0.606891
1/22/2020	1	0.439834
1/23/2020	0	0.896842
1/24/2020	0	0.147459

.....

My objective is to retain the next 180 observations of "value" every time event=1 (days do not have to be consecutive). At the end, I would like to have the following dataset:

obs_date	eventday	value	day_count
1/4/2020	1/4/2020	0.048467	0
1/5/2020	1/4/2020	0.718177	1
1/6/2020	1/4/2020	0.001364	2
1/7/2020	1/4/2020	0.440972	3
1/8/2020	1/4/2020	0.559622	4
1/9/2020	1/4/2020	0.821105	5
1/10/2020	1/4/2020	0.332539	6
1/11/2020	1/4/2020	0.289809	7
1/12/2020	1/4/2020	0.911323	8
1/18/2020	1/4/2020	0.896737	9
1/19/2020	1/4/2020	0.333354	10
…	1/4/2020	....	…
…	1/4/2020	....	180
1/10/2020	1/10/2020	0.332539	0
1/11/2020	1/10/2020	0.289809	1
1/12/2020	1/10/2020	0.911323	2
1/18/2020	1/10/2020	0.896737	3
1/19/2020	1/10/2020	0.333354	4
1/20/2020	1/10/2020	0.19605	5
1/21/2020	1/10/2020	0.606891	6
1/22/2020	1/10/2020	0.439834	7
1/23/2020	1/10/2020	0.896842	8
1/24/2020	1/10/2020	0.147459	9
1/25/2020	1/10/2020	0.923238	10
…	1/10/2020	...	…
…	1/10/2020	...	180

I could not figure out how to perform this data step. I would love to get your suggestions on it. Thank you so much for your help in advance.

Re: Remove extra spaces and combine letters

finans_sas — Wed, 30 Jun 2021 18:53:22 GMT

Thank you so much for your help, Ksharp! I will try your suggestion.

Re: Remove extra spaces and combine letters

finans_sas — Wed, 30 Jun 2021 18:52:21 GMT

Thank you so much for your help, Patrick! I will try your suggestion.

Re: Remove extra spaces and combine letters

finans_sas — Wed, 30 Jun 2021 18:45:31 GMT

Thank you so much, ChrisNZ for this code! I really appreciate it. When I used it, it was able to combine the letters the way I wanted, but it produces errors when I have names that do not need to be modified. For example, it converts "ABC Supply" to "ABCSupply ABC Supply" whereas the desired outcome here is the same as the actual outcome (ABC Supply). Is there a way to remedy this?

On a separate note, sometimes I have single letters in different parts of the names such as ABC U S Supply, which should be changed to ABC US Supply. However, there are not many of those.

Thank you so much!

Remove extra spaces and combine letters

finans_sas — Tue, 29 Jun 2021 20:56:41 GMT

Hello Everyone:

I have a column with names, but there are extra spaces in the names. I provided a sample dataset below. My goal is to remove the space between single letters and combine them into one word. However, I do not want to merge the single-letters with multi-letter words. Is it possible to write a script such that if it will search in a string and stop where a multi-letter word first appears (for example, the word Supply in the first case) and it will merge all the letters to the left of it? I would really appreciate your help with step.

Thank you so much

Actual	Desired
A B Supply Inc	AB Supply Inc
A B C Machines Inc	ABC Machines Inc
A B C D Operations Inc	ABCD Operations Inc
A B C D E US Inc	ABCDE US Inc

Re: Help with running a SAS macro (no output is generated)

finans_sas — Tue, 29 Jun 2021 20:45:09 GMT

Thank you so much, Reeza!