topic Re: Installments in SAS Programming

Installments

Tecla1 — Thu, 29 Oct 2020 07:47:54 GMT

Good morning to all,

please I need a solution to this problem can I have your help?

In attached an excell file to explain well….

Many tnks!

Re: Installments

Kurt_Bremser — Thu, 29 Oct 2020 08:54:42 GMT

Please provide SAS datasets in data steps with datalines, and the expected result in similar form or as a simple visual table.

Using a data step will make sure that you provide valid names for variables, and leave no questions with regards to variable attributes.

Your spreadsheet has columns without names, and invalid names for some columns.

Re: Installments

ballardw — Thu, 29 Oct 2020 14:34:19 GMT

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.

Text describing a process should be posted in the message as text, not an attachment.

Re: Installments

Tecla1 — Thu, 29 Oct 2020 15:13:09 GMT

Good afternoon, many tnks for your kindly help, in attached how many you request.

tnks, Tecla

Re: Installments

Kurt_Bremser — Thu, 29 Oct 2020 15:22:55 GMT

Your Excel spreadsheet can't be displayed in the online viewer; please supply the expected outcome from your dataset in text form.

Re: Installments

Kurt_Bremser — Thu, 29 Oct 2020 15:24:10 GMT

PS you can post nicely structured text by using this button:

Use the "little running man" right next to it for posting SAS code.

Re: Installments

Tecla1 — Sun, 01 Nov 2020 10:43:05 GMT

CHIAVE	STL_1	STL_2	STL_3	STL_4	STL_5	STL_6	STL_7	STL_8	STL_9	STL_10	STL_11	STL_12	STL_13	STL_14	STL_15	STL_16	STL_17	STL_18	STL_19
5540306388550000	56312.66	56373.9	56435.20	56496.58	56558.02	56619.52	56681.10	56742.74	56804.44	56866.22	56928.06	56989.97	57051.95	57113.99	57176.10	57238.28	57300.53	57362.81	
5540807460420000	3380.1	3388.66	3397.25	3405.86	3414.49	3423.15	3431.82	3440.52	3449.24	3457.98	3466.74	3475.53	3484.33	3493.16	3502.01	3510.89	3519.79	3528.71	3537.65
054Z0010951830000	11435.59	11549.94	11665.44	11782.10	11899.92	12018.90

CHIAVE NOV20 DEC20 JAN21 FEB21 MAR21 APR21 MAY21 JUN21 JUL21 AUG21 SEP21 OCT21 NOV21 DEC21 JAN22 FEB22 MAR22 APR22 MAY22 JUN22 JUL22 AUG22 SEP22 OCT22 NOV22 DEC22 JAN23 FEB23 MAR23 APR23 MAY23 JUN23 JUL23 AUG23 SEP23 OCT23 NOV23 DEC23 JAN24 FEB24 MAR24 APR24 MAY24 JUN24 JUL24 AUG24 SEP24 OCT24 NOV24 DEC24 JAN25 FEB25 MAR25 APR25 MAY25 JUN25 JUL25 AUG25 SEP25 OCT25 NOV25 DEC25 JAN26 FEB26 MAR26 APR26 MAY26 JUN26 JUL26 AUG26 SEP26 OCT26 NOV26 DEC26 JAN27 FEB27 MAR27 APR27 MAY27 JUN27 JUL27 AUG27 SEP27 OCT27 NOV27 DEC27 JAN28 FEB28 05540306388550000 56312.66 . . 56373.9 . . 56435.20 . . 56496.58 . . 56558.02 . . 56619.52 . . 56681.10 . . 56742.74 . . 56804.44 . . 56866.22 . . 56928.06 . . 56989.97 . . 57051.95 . . 57113.99 . . 57176.10 . . 57238.28 . . 57300.53 . . 57362.81 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 05540807460420000 . . . 3380.1 3388.66 3397.25 3405.86 3414.49 3423.15 3431.82 3440.52 3449.24 3457.98 3466.74 3475.53 3484.33 3493.16 3502.01 3510.89 3519.79 3528.71 3537.65 3546.61 3555.6 3564.61 3573.64 3582.7 3591.78 3600.88 3610.01 3619.16 3628.33 3637.52 3646.74 3655.98 3665.25 3674.53 3683.85 3693.18 3702.54 3711.92 3721.33 3730.76 3740.22 3749.69 3759.20 3768.72 3778.27 3787.85 3797.45 3807.07 3816.72 3826.39 3836.09 3845.81 3855.55 3865.32 3875.12 3884.94 3894.79 3904.66 3914.55 3924.47 3934.42 3944.39 3954.38 3964.4 3974.45 3984.52 3994.62 4004.74 4014.89 4025.06 4035.26 4045.49 4055.74 4066.02 4076.32 4086.65 4097.01 4107.39 4117.8 4128.24 4138.7 4149.19 4159.7 4170.24 4180.49 054Z0010951830000 . 11435.59 . . . . . 11549.94 . . . . . 11665.44 . . . . . 11782.10 . . . . . 11899.92 . . . . . 12018.90 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

Re: Installments

Tecla1 — Fri, 30 Oct 2020 09:07:55 GMT

Good morning,
I sent in a format as request from your Mr. Bremser, It' s good now ?
Tnks to All.
Tecla

Re: Installments

Kurt_Bremser — Sun, 01 Nov 2020 10:52:52 GMT

First of all, we need to get this into a usable shape:

proc transpose data=myfold.esempio_2 out=long (where=(col1 ne .));
by chiave notsorted;
var _numeric_;
run;

data long2;
set long;
period = input(_name_,monyy5.);
format period yymmd7.;
rename col1=value;
drop _name_;
run;

(I copied your dataset to the shared folder of my University Edition, where I have defined library MYFOLD)

From the final long dataset, you can now group and count by chiave (is this a code for a person?) and/or period; you can also do calculations, as period is stored as a SAS date value.

What we need to know now: how would the expected result from THIS data look like?

Re: Installments

Tecla1 — Mon, 02 Nov 2020 08:25:19 GMT

Good Morning,
Tnks for your help, I expect date as the example, where columns name are STL_1, STL_2 … ecc...)

Tecla

Re: Installments

Kurt_Bremser — Mon, 02 Nov 2020 10:16:56 GMT

How do you map the dates to the STL sequence? What is the logical rule for this?

Re: Installments

Tecla1 — Tue, 03 Nov 2020 08:30:02 GMT

Good morning, the order is the time…. but not the date …

Tnks !!!

Re: Installments

Kurt_Bremser — Tue, 03 Nov 2020 08:57:13 GMT

So you only want a sequence number, regardless of the difference between the dates?

data want;
set long2;
by chiave notsorted;
if first.chiave
then stl_count = 1;
else stl_count + 1;
run;

Or do you want the empty periods kept?

data want;
set long2;
by chiave notsorted;
retain _period;
if first.chiave then _period = period;
stl_count = intck('month',_period,period) + 1;
drop _period;
run;