Installments

[Tecla1]

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!

[Kurt_Bremser]

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.

[Tecla1]

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

tnks, Tecla

[Kurt_Bremser]

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

[Kurt_Bremser]

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

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

[Tecla1]

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	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.	.

[Kurt_Bremser]

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?

[Tecla1]

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

Tecla

[Kurt_Bremser]

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

[Tecla1]

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

Tnks !!!

[Kurt_Bremser]

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;

[ballardw]

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.

[Tecla1]

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